backtop


Print

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)





"So if you want to save the planet, feel free to drive your Hummer. Just avoid the drive thru line at McDonalds." -- Michael Asher













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