I need to OR some functional IF(AND sets

[eryn]

Greetings,

I'm wading into Excel formulas more than I am familiar with, and would
greatly appreciate being clued as to a manner to OR the following IF
(AND formulas into a single cell operation.

=(IF(AND(B6<=4,B5<=5),25,
IF(AND(B6<=4,B5>=41),65,
IF(AND(B6<=4,B5>=21),55,
IF(AND(B6<=4,B5>=11),45,
IF(AND(B6<=4,B5>=6),35)))))

=IF(AND(B6>=31,B5<=5),75,
IF(AND(B6>=31,B5>=41),175,
IF(AND(B6>=31,B5>=21),150,
IF(AND(B6>=31,B5>=11),100,
IF(AND(B6>=31,B5>=6),90)))))

=IF(AND(B6>=21,B5<=5),65,
IF(AND(B6>=21,B5>=41),165,
IF(AND(B6>=21,B5>=21),125,
IF(AND(B6>=21,B5>=11),95,
IF(AND(B6>=21,B5>=6),85)))))

=IF(AND(B6>=16,B5<=5),55,
IF(AND(B6>=16,B5>=41),125,
IF(AND(B6>=16,B5>=21),100,
IF(AND(B6>=16,B5>=11),85,
IF(AND(B6>=16,B5>=6),65)))))

=IF(AND(B6>=10,B5<=5),45,
IF(AND(B6>=10,B5>=41),90,
IF(AND(B6>=10,B5>=21),75,
IF(AND(B6>=10,B5>=11),65,
IF(AND(B6>=10,B5>=6),55)))))

=IF(AND(B6>=5,B5<=5),35,
IF(AND(B6>=5,B5>=41),75,
IF(AND(B6>=5,B5>=21),65,
IF(AND(B6>=5,B5>=11),55,
IF(AND(B6>=5,B5>=6),45)))))

Many thanks!

 

[Bernie Deitrick]

You need a table.

In A10 to F16, enter these values

X 0 5 11 21 41
0 25 35 45 55 65
5 35 45 55 65 75
10 45 55 65 75 90
16 55 65 85 100 125
21 65 85 95 125 165
31 75 90 100 150 175

Then use the formula

=INDEX(B11:F16,MATCH(B6,B10:F10),MATCH(B5,A11:A16))

to replace your family of formulas.

HTH,
Bernie
MS Excel MVP

 

[eryn]

This looks like a very slick solution ~ I shall most certainly give
this a shot and let you know how it goes

Thank you so much!