Have you ever entered a formula into Excel only to have it treat it as text and just sit there displaying what you typed without resolving it?
Working with models always seems to involve Excel – whether you are putting your data in spreadsheets directly, passing information in and out of the GRTMPS database, or generating reports. Here, as an example, is the SSI file for Process Unit Adherent Recursion data [Table 2xx.9]
I’m changing from property PPM to SUL (%wt) to improve the scaling of the partials, so I type that in the first Pup1 cell, and then, to simplify maintenance, set up a formula so that all the instances that need the same entry update automatically. Easy?
Except the formula just displays as text, without resolving. Pressing F9 (calculate now) won’t help. And it’s not that display option that allows you to see cell contents rather than values.
| Right click for“Format Cells”, and you will see that it has been formatted as Text. |
|
Switch the formatting over to General, so that you can use that location for a formula. |  |
The existing entry will still be treated as a text – but if you update it, it will be processed as a formula. An easy way to make it refresh is to delete the =
and then restore it.
You could also have removed the text formatting by copying over with a cell that was formatted as general, but if you happen to pick one that is also text you can get rather frustrated. If you have already typed in a complicated formula that would take time to enter again, then this method, which preserves your work, will be more efficient.
Text Format does have its uses. if a PUP say, was called 1E40 and it wasn’t formatted as text, it would be read as a number in scientific notation when entered in Excel. The code that generates the SSI has formatted the cells containing PUP codes as text so that sort of thing can’t happen. An alternative formatting trick that I find it quicker for manual entry is the "apostrophe text format marker". If you are trying to enter, for example, 01 and it is being treated as a number, and showing up as 1, type in '01 and the cell will display 01. This is also useful if you have text that starts with an equals sign. = TOTAL leaves you with #NAME?, but '=TOTAL, gives you =TOTAL.
From Kathy's Desk 27th March 2017.
Comments and suggestions gratefully received via the usual e-mail addresses or here.