CODEWITHSUNDEEP
excelif-statement
Binx
Binx

Reputation: 414

IF AND OR statement not computing correctly

I am having some trouble with my if statement, join with and and or. Could someone tell me why the selected cell is not returning the value of 2? It returns "" even though it is between 6.5 and 6.0. The other problem I am running into is when I format column R to have one decimal place, the selected cell should result in 1.

Column S equation: =IF(AND(R2<=H2+0.5,R2>=H2-0.5),1,"")

Column T equation: =IF(AND(OR(AND(R3<=H3+1,R3>H3+0.5),R3>=H3-1),R3<H3-0.5),2,"")

Column U equation: =IF(OR(S2=1,T2=2),"",3)

The goal of this is to determine how far R3 falls from H3 based on set thresholds. The thresholds are +-0.5, +-1, and outside +-1. For example R3 is larger than H3 + 0.5 and less than or equal to H3 + 1. So T3 should result with a 2.

enter image description here

Upvotes: 0

Views: 75

Answers (3)

SJR
SJR

Reputation: 23081

I think this works for you in a single formula.

Formula in C1 and down is 

=LOOKUP(ABS(B1-A1),{0,0.5,1},{1,2,3})

Upvotes: 0

Binx
Binx

Reputation: 414

I just tried this and it worked for me:

=IF(AND(R3<=H3+1,R3>H3+0.5),2,IF(AND(R3>=H3-1,R3<H3-0.5),2,""))

Upvotes: 1

Evil Blue Monkey
Evil Blue Monkey

Reputation: 2619

My formula for T3:

=IF(ABS(H3-R3)>1,"difference > 1",IF(ABS(H3-R3)>=0.5,"difference between 1 and 0.5","difference < 0.5"))

This VBA code will write you the formula on a selected cell in the T column:

ActiveCell.FormulaR1C1 = "=IF(ABS(RC[-1]-RC[-11])>1,""difference > 1"",IF(ABS(RC[-1]-RC[-11])>=0.5,""difference between 1 and 0.5"",""difference < 0.5""))"

You can change the text with numbers as pleased. Does this satisfy you?

Upvotes: 2

Related Questions