Nested IF: return "High" if cell value is >5, "Low" if <-5, and "Mid" if in between

[tiger63000]

I've tried so many options but it only seems to return one answer.

E.g. The values I have are [A1] 41, [A2] 0.08, [A3] -2 and [A4] -13.

In B1 I have: =IF(A1>5,"High",IF(A1<-5,"Low","Mid"))
This returns "High", which is correct.
But I drag it down into B2, B3, B4 and the cell ref A1 changes to A2 etc. as expected, BUT all returns are "High", when it should be:
B1 "High"
B2 "Mid"
B3 "Mid"
B4 "Low"

ANy clues as to what I"m doing wrong? Thanks soo soo much

 

[tiger63000]

All good- my spreadsheet was not automatically calculating - changed the settings and all good.

 

[Trebor76]

Hi tiger63000,

Welcome to MrExcel!!

Put this formula in cell B1 and copy down to cell B4:

=IF(AND(A1>=-5,A1<=5),"Mid",IF(A1>5,"High","Low"))

Hope that helps,

Robert