backtop


Print 9 comment(s) - last by chuckthenerd.. on Dec 31 at 4:43 PM

Want to create a list of nicely formatted numbers? Here's some tricks that took me a while to dig up...

So, I've been playing around in Microsoft Corp.'s (MSFT) Excel 2010 and have found out how to do some pretty cool things.  Now to the hardcore Excel user, you may laugh and say, "Well, duh!"

If you're one of those experts, beware, you may be in for what (seems) to you obvious.

I. The Problem

So I'm testing a range of integers in a C program, and I figured a quick way to get those numbers would be to dump a .CSV file from Excel.  To cut a long story short, I used blocks of formulas:

=128        <-- (in B2)
=B2+1
=ROUND(1.2*B2)
=ROUND(1.4*B2)
=ROUND(1.5*B2)
=ROUND(1.6*B2)
=ROUND(1.8*B2)
=2*B2+1

Which I then copied multiple times till I had my desired range.  This was pretty quick -- I knew how to do it.  Most Excel users know how to do this.

But then the bright idea came into my head to add nicely formatted labels in the first column, e.g. 28-1, 28, 1.2 x 28... and so on.

Well this turned out to be a bit harder than I thought (but not impossible!).  I wanted to this formulaically, and after much study I was able to create a somewhat magical formula, which relies on two tricks, one more commonplace (but still a refresher for me) and one more advanced.

The formulas I used for my previous example labels were:

="2"&IF(FLOOR.PRECISE(LOG(B9,2)+1)>9,VLOOKUP(FLOOR.PRECISE(FLOOR.PRECISE(LOG(B9,2)+1)/10),$J$7:$K$16,2,FALSE),"")&VLOOKUP(FLOOR.PRECISE(MOD(LOG(B9,2)+1,10)),$J$7:$K$16,2,FALSE)&"-1"

="2"&IF(FLOOR.PRECISE(LOG(B10,2))>9,VLOOKUP(FLOOR.PRECISE(FLOOR.PRECISE(LOG(B10,2))/10),$J$7:$K$16,2,FALSE),"")&VLOOKUP(FLOOR.PRECISE(MOD(LOG(B10,2),10)),$J$7:$K$16,2,FALSE)

="2"&IF(FLOOR.PRECISE(LOG(B11,2))>9,VLOOKUP(FLOOR.PRECISE(FLOOR.PRECISE(LOG(B11,2))/10),$J$7:$K$16,2,FALSE),"")&VLOOKUP(FLOOR.PRECISE(MOD(LOG(B11,2),10)),$J$7:$K$16,2,FALSE)&"+1"

Note these are more complex examples of connecting pieces of text, e.g.:

="Hello"&"World"&"!"

II. Magic Formula Superscripts

So you may have noticed the trick from the previous formulas.  If so, you can quit here.  Otherwise, forge ahead.

I had to find a way to display a subscript in a formula.  Well, Excel (to my knowledge, and as far as my research indicated), does not have the ability to selectively display a superscript inside a formula with normal text.  Perhaps someday it will (perhaps via special characters in the format string), but for now it seems this functionality is missing.

But fear not.

What you can do is use special characters that look like superscripts, and then use a lookup table grab the appropriate superscripts.  I give this lookup table below:

0.00 °
1.00 ¹
2.00 ²
3.00 ³
4.00 4
5.00 5
6.00 6
7.00 7
8.00 8
9.00 ?

You can copy and paste it into some cells and then turn the text white, if you wish to hide it.

Now use the formula:

="[BEFORE TEXT] " & IF(FLOOR.PRECISE(LOG(B10,$A$3))>9,VLOOKUP(FLOOR.PRECISE(FLOOR.PRECISE(LOG(B10,$A$3))/10),$J$7:$K$16,2,FALSE),"")&VLOOKUP(FLOOR.PRECISE(MOD(LOG(B10,$A$3),10)),$J$7:$K$16,2,FALSE) & "[AFTER TEXT]"

Now put your base in cell A3 (you can hide it in white if you wish) or change it a different cell.  If you change the cell, you will have to change the absolute index.  That brings us to our first trick -- absolute indices.  

Ever wanted to get a bunch of formulas to use the same cell without offsetting when you copy and paste?  No problem.  Just add a '$' before the column letter and the row number.

So many know this.  But the part you might not now is the VLOOKUP function, which takes four arguments -- a search value (in this case our log, floored to the closest integer), a search range ("table array" in Microsoft-speak), which column to take your match result from, and a flag indicating whether to include only exact matches ("false) or approximate matches, as well ("true").

In this case I use the VLOOKUP to grab the appropriate superscript-looking character.  I then chain together an "IF", which grabs the second least significant digit by doing a VLOOKUP for the floored result of the superscript number divided by 10, and the least significant digit which is given by a VLOOKUP search on a floored mod 10 call on the superscript number.  You can extend this approach to accomodate more than 2 digits, by adding additional "IF" statements, in front of my first if.

III. The Results: Pretty Numbers

The result of all this formula magic looks something like:

Excel view

Again, as you see, I've hidden my integer/superscript lookup table over in cells J7:K16 ... nifty, eh?

Well, hopefully this will save someone out there some time.  It took me a while to figure out how to do this, but you can just read this tutorial and benefit from my time investment!

For more technical reading, Microsoft's searchable help documents are of course one route, and you can also wander over to the "Excel functions" directory of the Office website to find out more.

Source: Microsoft (VLOOKUP)



Comments     Threshold


This article is over a month old, voting and posting comments is disabled

lol whut?
By ViroMan on 12/12/2011 2:50:36 PM , Rating: 2
Page is broken?




RE: lol whut?
By Norseman4 on 12/12/2011 6:51:42 PM , Rating: 2
Not really, just no spaces in the very long line of formula for the browser to word-wrap.


RE: lol whut?
By tastyratz on 12/13/2011 4:43:00 PM , Rating: 2
OR
page is broken since it is not just the formula.
nothing is being wrapped and it is all running over, it is a cluster...


RE: lol whut?
By ZaethDekar on 12/13/2011 8:06:15 PM , Rating: 2
I am using IE 8 and I don't have any issues on this page.


RE: lol whut?
By althaz on 12/22/2011 4:46:18 AM , Rating: 2
Works fine in newer versions of IE as well. Fails pretty hard in Firefox (my normal browser)


RE: lol whut?
By lewisc on 12/30/2011 6:16:21 AM , Rating: 2
I agree - the text as well as formulae are not wrapping, meaning that large parts of the page are lost in the ad-border. I've used my normal browser, firefox, as well as Safari and the problem is consistent between the two.


LOL
By Visual on 12/19/2011 11:34:11 AM , Rating: 2
"Well, duh".

But really, this strikes me as a completely backwards approach. Instead of starting from the number and trying to work out a nice representation for it, you should have started with the nice representation and worked out the number. Have three columns for power, multiplier, addition. Assemble both the label and number from these.

Also, have some spaces around concatenation operator "&", after the function argument separator "," and such, and maybe you wouldn't be breaking my browser. Or have your CSS guys get a clue.

And what program do you need this for?




RE: LOL
By RivuxGamma on 12/28/2011 6:26:22 PM , Rating: 2
Meh, cut him some slack. Mr. Mick ain't exactly a professional Excel user. He's a tech blog writer.


By chuckthenerd on 12/31/2011 4:43:00 PM , Rating: 2
Save function kung-fu for the easy stuff, and break out a user-defined function in Excel VBA for the heavy lifting...

Something like http://www.eng-tips.com/viewthread.cfm?qid=138208 seems relevant since it does super and subscripting.




“Then they pop up and say ‘Hello, surprise! Give us your money or we will shut you down!' Screw them. Seriously, screw them. You can quote me on that.” -- Newegg Chief Legal Officer Lee Cheng referencing patent trolls














botimage
Copyright 2014 DailyTech LLC. - RSS Feed | Advertise | About Us | Ethics | FAQ | Terms, Conditions & Privacy Information | Kristopher Kubicki