Help to shorten formula?

[bjbalmforth]

I am trying to get a formula that will do the following scoring system, it depends on different cell numbers to award points:

If E14=3 & E16=0 Then if E18=3 then 2
E18=4 then 1
E18=2 then 3
E18=1 then 4

If E14=3 & E16=1 then if E18=3 then 3
E18=4 then 2
E18=5 then 1
E18=2 then 4
E18=1 then 5

If E14=3 & E16=2 Then if E18=3 then 4
E18=4 then 3
E18=5 then 2
E18=6 then 1
E18=2 then 5
E18=1 then 6


If E14=4 & E16=0 then if E18=4 then 2
E18=5 then 1
E18=3 then 3
E18=2 then 4
E18=1 then 5


If E14=4 & E16=1 Then if E18=4 then 3
E18=5 then 2
E18=6 then 1
E18=3 then 4
E18=2 then 5
E18=1 then 6


If E14=4 & E16=2 then if E18=4 then 4
E18=5 then 3
E18=6 then 2
E18=7 then 1
E18=3 then 5
E18=2 then 6
E18=1 then 7


If E14=5 & E16=0 then if E18=5 then 2
E18=6 then 1
E18=4 then 3
E18=3 then 4
E18=2 then 5
E18=1 then 6


If E14=5 & E16=2 Then if E18=5 then 4
E18=6 then 3
E18=7 then 2
E18=8 then 1
E18=4 then 5
E18=3 then 6
E18=2 then 7
E18=1 then 8


If E14=4 & E16=2 then if E18=4 then 4
E18=5 then 3
E18=6 then 2
E18=7 then 1
E18=3 then 5
E18=2 then 6
E18=1 then 7

I came up with the following formula but can not complete the last part as it is to long to fit in the cell.

=IF(B10=4,+IF(B11=0,+IF(B13=4,2)+IF(B13=5,1)+IF(B13=3,3)+IF(B13=2,4)+IF(B13=1,5)))+IF(B10=4,+IF(B11=1,+IF(B13=4,3)+IF(B13=5,2)+IF(B13=6,1)+IF(B13=3,4)+IF(B13=2,5)+IF(B13=1,6)))+IF(B10=4,+IF(B11=2,+IF(B13=4,4)+IF(B13=5,3)+IF(B13=6,2)+IF(B13=7,1)+IF(B13=3,5)+IF(B13=2,6)+IF(B13=1,7))+IF(B10=3,+IF(B11=2,+IF(B13=3,4)+IF(B13=4,3)+IF(B13=5,2)+IF(B13=6,1+IF(B13=2,5)+IF(B13=1,6)))))+IF(B10=3,+IF(B11=0,+IF(B13=3,2)+IF(B13=4,1)+IF(B13=2,3)+IF(B13=1,4)))+IF(B10=3,+IF(B11=1,+IF(B13=3,3)+IF(B13=4,2)+IF(B13=5,1)+IF(B13=2,4)+IF(B13=1,5)))+IF(B10=3,+IF(B11=2,+IF(B13=3,4)+IF(B13=4,3)+IF(B13=5,2)+IF(B3=6,1)+IF(B13=2,5)+IF(B13=1,6)))+IF(B10=5,+IF(B11=0,+IF(B13=5,2)+IF(B13=6,1)+IF(B13=4,3)+IF(B13=3,4)+IF(B13=2,5)+IF(B13=1,6)))+IF(B10=5,+IF(B11=1,+IF(B13=5,3)+IF(B13=6,2)+IF(B13=7,1)+IF(B13=4,4)+IF(B13=3,5)+IF(B13=2,6)+IF(B13=1,7)))

Can anybody help me with shorting this formula and to make it work as outlined above.

Your help will be much appreciated.

 

[colbymack]

How about setting up a chart, and doing a vlookup against that chart of the concatenated column values - i.e.

If E14=3 & E16=0 Then if E18=3 then 2
E18=4 then 1
E18=2 then 3
E18=1 then 4

This could be handled as a vlookup agains a table that has the following 2 columns (in F1 to G4, for my example below).
303 2
304 1
302 3
301 4

Your cell that you want to get the resulting value in would say vlookup (concatenate(e14,e16,e18),F1:G20,1,false)

Where F1 to G20 houses the lookup table that I mentioned above for all of your values (I am not sure if there were actually 20 or not).
So, if the concatenated value =303, then 2, if 304, then 1, and so on.

Does that work for you?

I have also seen a different place that uses various forms of the concatenate to get around the 7 nested if's posted on this forum, if that is the problem you are experienceing.

HTH

 

[bjbalmforth]

i need this formula to be in each cell as there are 18 columns and this format occurs 30 time, that why it needs to be in each individual cell.

 

[colbymack]

You can put the formula in all of the cells in which you are interested in deriving a value. The trick is having all of the possible answer combinations in a separate table (could be on a separate worksheet and/or workbook, or it could be off to the right or below your existing data).