Archive for January, 2009

Excel and fractions

Wednesday, January 7th, 2009

I’ve been working with MS Excel quite a bit lately, and one of the things that’s been driving me nuts is fractions. You can represent an improper fraction as text, and you can format a proper fraction numerically, but whenever you use it in a formula it’s the decimal that’s utilized. Given a large spreadsheet with a mixture of numbers, fractions, mixed numbers, and text already there, how do I make sure that my numeric fractions are preserved in my formulas in other cells?

Well, I decided to convert them to text. Automatically. I made a formula that did this, and I want to save it here for future reference:

=IF(ISNUMBER(A2),TEXT(A2,”### ###/###”),A2)