Thread: IF function inconsistent--formatting related?

Hello,

I created a spreadsheet template. When creating a new spreadsheet from the template, one of the formulas works inconsistenly, and I can't figure out why. I have attached a slimmed down broken version of a spreadsheet created from the template. All of the relevant columns and look-up tables are included, as well as a sample of the original data, and the error still happens in the stripped down version, to wit:

- The formula in question is in Column K.
- If the value in Column A is "Off property", then the value in Column K should be 0.
- Note that it works in the rows where the value in Column A is not "Off property".
- I found that if I
- typed over, or cleared and typed over the existing data in an Off property cell in Column A, the formula would not work correctly
...but if I
- selected a cell in Column K and highlighted the text "Off property" in the formula and cut and pasted it into an Off property cell in Column A, the formula would work correctly.
- I tried to see if there were differences in the formatting of the Subject property cells vs the Off property cells and other than color, could find no differences.
[Sub-Question: is there a way to view the formatting of a cell?]

What am I missing? Why is this formula acting flaky?

Thanks for the extra eyes and help.

copperberry

Some of the Off Property strings in column A have a trailing space.. you should fix those.

Otherwise revise formula to reference A2 with TRIM(A2)

i.e. =IF(TRIM(A2)="Off property",0,...)

Btw, the formula is not in column K... it appears to be in column N.

Thank you, NBVC! That was it. I knew I needed another pair of eyes. And thank you for introducing me to the TRIM function. That is a handy item!

Cheers,

copperberry

PS: Some of the columns were hidden in my version of the sheet. I assume that is why my Column K was your Column N.