simplify and if formula

[d0wnt0wn]

hey guys,

I am wondering if there is a more simple way to do this

=IF(AND(B3=8,C3="B"),4,IF(AND(B3=9,C3="B"),7,IF(AND(B3=7,C3="A"),0,IF(AND(B3=8,C3="A"),2,IF(AND(B3=9,C3="A"),5,IF(AND(B3=7,C3="B"),2,IF(AND(B3=7,C3="C"),4,IF(AND(B3=8,C3="C"),6,IF(AND(B3=9,C3="c"),9,IF(AND(B3=7,C3="B"),2,IF(AND(B3=7,C3="P"),-2,IF(AND(B3=8,C3="P"),0,IF(AND(B3=9,C3="P"),3,"")))))))))))))

essentially its looking for 2 criteria to give me a value. For example if cell B3=8 and C3=A the result is 2..... 8 is woth 2 points and A is worth 0... but what i really want is to make it so i can easily add more criteria to get my result and be able to change the value of each of those criteria without having to change the whole formula each time

 

[jasonb75]

B3 in your formula contains a number, either 7, 8 or 9 so B3-6 will be either 1, 2 or 3 (7-6, 8-6 or 9-6).

It works with this, but as I said in post 8, if any changes or additions don't follow the same pattern then you will have problems.

 

[Fluff]

It simply subtracts 6 from the value in B3.

 

[Z51]

All of your values in B3 are 7, 8 or 9. Subtracting 6 leaves you with 1, 2 or 3, which will return the first, second or third arguments after that in the CHOOSE function.

 

[d0wnt0wn]

All of your values in B3 are 7, 8 or 9. Subtracting 6 leaves you with 1, 2 or 3, which will return the first, second or third arguments after that in the CHOOSE function.

thank you i literally just realized that 5 minutes ago

 

[d0wnt0wn]

thanks you all for your help i ended up using multiple formulas and index charts for different things

 

[Fluff]

Glad we could help & thanks for the feedback.