Formula with mutltiple conditions

[Guest]

Hi there!

I have a problem creating a formula that would contain multiple conditions
with only one possible answer.

If a5=1 and b5=0 and c5=1 and d5=1 Then e5=1
If a5=1 and b5=0 and c5=1 and d5=2 Then e5=1
If a5=1 and b5=0 and c5=2 and d5=1 Then e5=1
If a5=1 and b5=0 and c5=2 and d5=2 Then e5=1
If a5=1 and b5=0 and c5=3 and d5=1 Then e5=2
If a5=1 and b5=0 and c5=3 and d5=2 Then e5=2
If a5=2 and b5=1 and c5=1 and d5=1 Then e5=2
If a5=2 and b5=1 and c5=1 and d5=2 Then e5=2
If a5=2 and b5=1 and c5=2 and d5=1 Then e5=2
If a5=2 and b5=1 and c5=2 and d5=2 Then e5=3
If a5=2 and b5=1 and c5=3 and d5=1 Then e5=3
If a5=2 and b5=1 and c5=3 and d5=2 Then e5=4
If a5=2 and b5=2 and c5=1 and d5=1 Then e5=3
If a5=2 and b5=2 and c5=1 and d5=2 Then e5=4
If a5=2 and b5=2 and c5=2 and d5=1 Then e5=4
If a5=2 and b5=2 and c5=2 and d5=2 Then e5=5
If a5=2 and b5=2 and c5=3 and d5=1 Then e5=5
If a5=2 and b5=2 and c5=3 and d5=2 Then e5=6

Is it possible? Help!

Thanks in advance for your help!

Greywolf

 

[Guest]

I would CONCATENATE the four cells into a helper cell. Then do a LOOKUP or
VLOOKUP on that cell to return the result.


Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Thanks CLR.

I forgot to mention that rows A-B-C and D are part of a database and I'm
wondering if I do the LOOKUP or VLOOKUP is going to interfere with the
sorting of the database.

 

[Guest]

you could try an equation like
=IF(B5=0,IF(C5<3,1,2),IF(B5=1,IF(C5+D5<4,2,C5+D5-1),C5+D5+1))

 

[Guest]

It may slow it down some if the database is large.......but you can always do
Copy > PasteSpecial > Values on the formula columns to get rid of the
formulas and just keep the results.........
Actually you can just build your Vlookup Table a few rows to the right of
the database so there will be no interaction.....name it "MyTable", and then
just put this formula in E5 ad copy it down, then Copy > PasteSpecial >
Values on it if you wish later.

=VLOOKUP(A5&B5&C5&D5,MyTable,2,FALSE)

All that said, BJ's formula does the whole thing with less effort.........


Vaya con Dios,
Chuck, CABGx3