Forum Discussion
Excel using vlookup not calculating after 3 steps in spreadsheet---#NAs start "pop up"
Hello,
This is not an issue with formulas not working, but with data entry.
Looking at the formula in Sheet "Computations", cell B8. Unravelling the formula, it turns out that the value "3X" is being looked up with VLookup in a table that shows "3X" as a lookup value, but the actual content of the cell is just a number 3 that has been formatted with a custom format to appear as "3X".
The value ultimately comes from your Input form, where the value "3X" has been entered into cell B7.
The cell is formatted to show numbers with an "X" following, so the "X" does not need to be typed into this cell. If you simply type the number 3 into this cell, it will still appear as "3X" because of the formatting and all the follow-on formulas will calculate correctly.
Let me know if that helps.
I have tried it with and without the ##X format. Still does not work.
The 3X is converted to a 3 on the Rate page in the section of Frequency Conversion to indicated which column to select the rate amount for the Column A
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.
