conditional formula outputting the wrong result

[kylefoley76]

my formula is as follows

=IF(H69<-99,H69-100,IF(H69<-9,H69-10,H69-1))

when h69 = -12, the formula outputs -13 whereas it should output -22. Then I manually retype -12 in cell h69 then I get the correct result. The cells are in number format, so I don't understand what is going on.

 

[sinoyon780]

• Ensure that the cell H69 is formatted as a number, not as text. If it is formatted as text, the formula will not work correctly.
• If the cell is already formatted as a number, check if there are any leading or trailing spaces in the cell value that might cause it to be recognized as text. To fix this, select the cell, press F2 to edit the cell, and then press Enter.

 

[awoohaw]

Do you have calculation off? Is it a big worksheet that needs to do lots and lots of calculations?
I just plugged it in and got -22 as well.

 

[kylefoley76]

at one time the column was formatted as text but i select the entire column and switch it to number, i then add one decimal place to make sure that it's no longer a text and this time all the cells that are giving me problems do not show up with a decimal place. I can't manually go through and fix 300 cells. I need to be able to quickly shift from text to number with no problems. I need to find a way to be able to convert text to number reliably and easily. i'm using excel for mac 16.66

 

[Peter_SSs]

Sounds like H69 is being treated as text as already mentioned. Another option is to leave H69 that way, and instead alter your formula structure slightly.

Here you can see that H69 is text but -22 is returned by the reformatted formula.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

23 01 30.xlsm
	H	I	J
69	-12	-22	TRUE
Formula

Cell Formulas
Range	Formula
I69	I69	=IF(H69+99<0,H69-100,IF(H69+9<0,H69-10,H69-1))
J69	J69	=ISTEXT(H69)

 

[kylefoley76]

Thanks for helping me out but I still don't understand how I am supposed to change the cells into number format, since changing the whole column to number format does not work. I also don't understand how to get the correct result if the cell is left as text format

 

[Peter_SSs]

I also don't understand how to get the correct result if the cell is left as text format

Did you try the formula that I put in cell I69? (Make sure column I is not Text format before entering the formula)