Formula with mutltiple conditions
 

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
--
Thanks !

Greywolf

Formula with mutltiple conditions
 

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




"Greywolf" wrote:

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
--
Thanks !

Greywolf

Formula with mutltiple conditions
 

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.

--
Thanks !

Greywolf


"CLR" wrote:

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




"Greywolf" wrote:

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
--
Thanks !

Greywolf

Formula with mutltiple conditions
 

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

"Greywolf" wrote:

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
--
Thanks !

Greywolf

Formula with mutltiple conditions
 

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



"Greywolf" wrote:

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.

--
Thanks !

Greywolf


"CLR" wrote:

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




"Greywolf" wrote:

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
--
Thanks !

Greywolf