Excel Magic: Superscripts in Formulas and More
December 11, 2011 9:28 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 (
) 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:
<-- (in B2)
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. 2
, 1.2 x 2
... 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:
Note these are more complex examples of connecting pieces of text, e.g.:
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:
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:
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 "
" directory of the Office website to find out more.
"So if you want to save the planet, feel free to drive your Hummer. Just avoid the drive thru line at McDonalds." -- Michael Asher
Latest By Jason Mick
Retiree Sues Apple For $7,500 for Wiping Honeymoon Photos From His iPhone
November 30, 2015, 10:23 AM
Creationists are Mad About Google Doodle Depicting Evolution
November 24, 2015, 8:48 PM
iPhone 7 May Pack 3-4 GB Memory, More Storage; 4-Inch Comeback is Rumored
November 20, 2015, 10:12 PM
DHS and TSA: Whoops, We Missed That 73 Airport Employees May be Terrorists
November 19, 2015, 2:16 PM
OnePlus One, OnePlus 2 Will Receive Android Marshmallow in Q1 2016
November 16, 2015, 9:58 AM
Twitter Senior VP: "Diversity is Important, But We Can’t Lower the Bar"
November 9, 2015, 9:59 AM
"Prepare to be Punished": Microsoft is Killing OneDrive With Cuts, Blames Users
November 3, 2015, 8:23 PM
True Beef or Sloppy Sausage? Digging Into the Red Meat + Cancer Correlation
October 27, 2015, 3:05 AM
Lenovo Whoa: Motorola Droid MAXX 2 and Turbo 2 Break Cover in Leaks
October 26, 2015, 3:12 PM
Leak: Apple Preps for First Real Android App Foray With New Apple Music App
October 24, 2015, 1:59 PM
Not All the High-Tech Jobs Are in California
August 4, 2016, 8:29 PM
Google's Gleaming Glass HQ Gets Mountain View Snub, LinkedIn Gets the Love
May 7, 2015, 6:58 AM
Tech's Tax Day Fortunate Few: Qualcomm, Xerox, GE, et al. Pay Little or No Taxes
April 15, 2015, 11:30 AM
LinkNYC Terminals to Blanket New York City With Free WiFi, Free Calls, and Ads
November 17, 2014, 6:50 PM
Microsoft is Open-Sourcing Most of .NET, Adding OS X and Linux Support
November 12, 2014, 8:27 PM
Home Depot Lost 53 Million Emails, Blames Windows, Buys Execs New Macs
November 9, 2014, 5:00 PM
Most Popular Articles
Gigabit Are you looking for an Ultra Compact board?
February 22, 2017, 6:30 AM
Dell XPS 13 Touch – Rose Gold Edition
February 23, 2017, 7:29 AM
ASUS ROG Maximus IX - Among the Best of the Best
February 21, 2017, 7:50 AM
Comparison: Tag Heuer Connected Modular VS Samsung Gear S3
February 20, 2017, 7:20 AM
Xiaomi Mi Note 2 – This Chinese Phablet is the Best
February 24, 2017, 7:25 AM
Latest Blog Posts
Gaming News of The Day
Feb 28, 2017, 6:56 AM
How much Ram do you need for gaming?
Feb 27, 2017, 6:00 AM
Interesting News of the Day
Feb 25, 2017, 7:37 AM
AMD and More
Feb 24, 2017, 5:55 AM
Feb 23, 2017, 6:30 AM
Feb 21, 2017, 6:12 AM
Here is how startups are helping new parents in raising children
Feb 20, 2017, 6:45 AM
Around the World
Feb 18, 2017, 5:48 AM
News of Future
Feb 17, 2017, 6:30 AM
Amazon parachutes May Float Packages to Customers
Feb 16, 2017, 8:00 AM
Now you Can Watch Facebook on Your TV
Feb 15, 2017, 7:42 AM
Feb 14, 2017, 5:36 AM
Razer Blade Stealth – Little Kaby Lake Powerhouse
Feb 13, 2017, 7:50 AM
Android 7.0 Nougat 7.0 Update Bring Less Battery Life for Samsung Galaxy S7 & S7 Edge
Feb 12, 2017, 7:45 AM
Apple iPhone 8 – OLED Display & Wireless Charging
Feb 11, 2017, 8:09 AM
Feb 10, 2017, 6:15 AM
Feb 9, 2017, 6:00 AM
Eye catching news
Feb 8, 2017, 6:16 AM
Some World News
Feb 7, 2017, 6:15 AM
Feb 6, 2017, 10:11 AM
Feb 5, 2017, 7:27 AM
More Blog Posts
Copyright 2017 DailyTech LLC. -
Terms, Conditions & Privacy Information