Forum Discussion
Excel using vlookup not calculating after 3 steps in spreadsheet---#NAs start "pop up"
Thanks
I reformatted cell B4 to just a general number and that is what is displayed. Now all the #NAs are gone the all the calculations are correct. I would like for B4 to display as 3X and not just a 3.... from 2005 up to 2015, that is the way it worked. I just did a custom format to save a keystroke. Oh well.
Again, thanks for the help.
You need to understand the difference between a cell's value and a cell's format.
The formulas totally ignore the cell format and will only evaluate the cell value, i.e. the underlying value "3" in this case.
You mix up presentation and data in your setup and that can be confusing.
Make sure that people only enter a "3" and not a "3X". Because your formulas only work on the value `3`, which in some scenarios has been formatted to display as a 3X.
That means that you can format cell B4 with the custom format to display any number with a trailing "X", but the actual value of the cell is still just the number that was entered.
Make sure to use data validation so only numbers can be entered into the cell. That will prevent the confusion that arises when people enter the text value "3X" into the cell.