Using AND OR with nested IF

[Emile du Toit]

[TABLE="width: 800"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]ROW 2[/TD]
[TD]X[/TD]
[TD]19[/TD]
[TD][/TD]
[TD]23[/TD]
[TD]45[/TD]
[TD]18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ROW 3[/TD]
[TD]Y[/TD]
[TD]99[/TD]
[TD]103[/TD]
[TD]100[/TD]
[TD]123[/TD]
[TD]88[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ROW 4[/TD]
[TD]Z[/TD]
[TD]987[/TD]
[TD]1001[/TD]
[TD]1020[/TD]
[TD]888[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am trying to populate column H, using data from the bolded columns. What the formula is supposed to do is compare C2 and D2 and provided they are not both blank, it needs to use the larger of the two (C>=D or D>C). Let us call this value CD.
It is going to be comparing this value as a percentage of E2, with a few rules involved:
a. 0 < CD2 <= 20, then (CD2+5) / E2*100
b. 20 < CD2 <= 100, then (CD2 + 10) / E2*100
c. 100 < CD2, then (CD2 / E2)*100

The formula I have created produces values, but is giving incorrect values on occasions, so clearly I havn't quite tied it down.

=IF(AND(OR(20<c2<=100, 20<d2<="100),AND(C2">=D2)),(C2+10)/E2*100,IF(AND(OR(20<c2<=100, 20<d2<="100),AND(D2">C2)),(D2+10)/E2*100,IF(AND(OR(0<c2<=20, d2<="20),AND(C2">=D2)),(C2+5)/E2*100,IF(AND(OR(0<c2<=20, d2<="20),AND(D2">C2)),(D2+5)/E2*100,IF(AND(OR(C2>100, D2>100),AND(C2>=D2)),C2/E2*100,D2/E2*100)))))

Please can you help me plug the leaks!!!!!</c2<=20,></c2<=20,></c2<=100,></c2<=100,>

 

[ukmikeb]

Apologies. it just demonstrates my ignorance. I thought that was what those brackets denoted.
So with your formula
=(MAX(C2,D2)+LOOKUP(MAX(C2,D2),{0,21,101},{5,10,0}))/E2*100
I could just tweak it to
=(MAX(C2,D2)+LOOKUP(MAX(C2,D2),{0,20.000001,100.000001},{5,10,0}))/E2*100And this would mean 0-20 (+5), 20.000001 - 100 (+10), 100.000001 and up (+0) ?
Thank you!

Yes that's correct.

The brackets around the MAX and LOOKUP formulae was to ensure that that calculation occurred before the division.

Pleased to have helped you find a solution.

Do come back again if you have further questions.