Using AND OR with nested IF

[Emile du Toit]

A	B	C	D	E	F	G	H
ROW 2	X	19		23	45	18
ROW 3	Y	99	103	100	123	88
ROW 4	Z	987	1001	1020	888

<tbody>
</tbody>

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,>

 

[snjpverma]

what if one of the cells C2 or D2 is blank ?

 

[snjpverma]

Try this and let me know if it doesn't work

=IF(MAX(C2,D2)=MEDIAN(0,20,MAX(C2,D2)),(MAX(C2,D2)+5)/E2*100,IF(MAX(C2,D2)=MEDIAN(21,MAX(C2,D2),100),(MAX(C2,D2)+10)/E2*100,MAX(C2,D2)/E2*100))

 

[ukmikeb]

Possibly :-

=(MAX(C2,D2)+LOOKUP(MAX(C2,D2),{0,21,101},{5,10,0}))/E2*100

round as required.

hth

 

[snjpverma]

Possibly :-

=(MAX(C2,D2)+LOOKUP(MAX(C2,D2),{0,21,101},{5,10,0}))/E2*100

Nice approach ukmikeb

 

[Emile du Toit]

Thanks snjpverna! It appears to be spitting out only correct values! Although ukmikeb has a very succinct formula, but for various reasons connected to updating I particularly do not want to use array formulas.

I have two questions - that are just connected to my own ignorance. I am trying to understand what your formula implies in terms of the changeover of values.
Firstly, does your formula mean that a value of exactly 100 gets the formula '+10/E2' or '/E2'
Secondly, what happens to values from 20 to 21?

 

[Emile du Toit]

Very neat solution. Thank you. In my particular case though I want to avoid array formulas.

 

[ukmikeb]

Very neat solution. Thank you. In my particular case though I want to avoid array formulas.

If you are referring to my solution it certainly is NOT an array formula (ie a formula that requires entry with Control-Shift-Enter)!

But there are arrays in there to return the required values which could quite easily be substituted a 2-column matrix on the worksheet.

And the values can be adjusted accordingly say 20.001 and 100.001.

hth

@snjpverma thanks for your comment in post #5

 

[Emile du Toit]

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*100
And this would mean 0-20 (+5), 20.000001 - 100 (+10), 100.000001 and up (+0) ?
Thank you!

 

[snjpverma]

Glad to see you find a solution !! Cheers