The dark side of ...
... Excel
There are situations, where Excel 97 and Excel 2000 do not perform the way wanted or even wrongly.
Situation 1:
You entered a number into a cell and formatted the cell as Text. The cell content will be shown as text now.
Now, change the format of the cell again, to be a number - either using the build-in functionality or XLplus Smart Numbers.
Result: The cell content is still shown as Text.
Situation 2:
You wish to display a function within a cell (i.e. for demonstration). To do that you select the cell and format it as text. Afterwards, you select the cell again, place the cursor somewhere within the cell and press Enter. Now the function will be displayed.
Alternatively use this tool.
Situation 3:
You entered some numbers in column A and some functions to work with. You then want to copy the values, the functions calculated, into a new range. Another function then will work with the value of the data pasted.
I.e. the data and functions look like
The functions entered within the range B3:B5 produce a string. Now paste the range as value within range A9:A11. The functions within B9:B11 do not get refreshed. You have to manually refresh the cells within A9:A11 by selecting each cell and pressing Enter.
Alternatively, use XLplus - Enter to correctly calculate the active selection of cells.
Other ...
Sort Data, Advanced Filter ...
The described will happen whenever you work with data of type text. Since this might affect functions results (i.e. if condition1 then return an empty string) you should check your spreadsheets carefully.
Use XLplus, Enter ... to correctly calculate the active selection of cells. This will select every cell within the active selection and write its content back into the cell - the same as selecting the cell, placing the cursor behind the last character and pressing Enter.
Use [Ctrl]+[Shift]+E alternatively.
Note: Pressing F9 (TOOLS - OPTIONS - CALCULATION - Calc Now) will not solve the above described problems.
Note: XLplus, Enter ... will also work with functions entered in the selection - even with Array functions used.