| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple conditions in a formula | Excel Worksheet Functions | |||
| an excel formula for specific conditions | Excel Worksheet Functions | |||
| Match then lookup | Excel Worksheet Functions | |||
| Formula to return cell contents based on multiple conditions | Excel Worksheet Functions | |||
| Help for a formula using two conditions | Excel Worksheet Functions | |||
Linear Mode
