Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help
Hi, I have this formula which is half working. I need it to say first of all
If cell AN="AIR" and the AX="Fri" then add 3 days to AJ. if AX = "sat" & AN="air" add 2 days to AJ. Otherwise if AN = "air" and the AX is any of the days not mentioned above add 1 to AJ. After that if AN is blank and AX="mon" add 4 to AJ, if ax ="tue" and 6 to AJ.... so on and so fort. The formula as I have does not pick up the last set of values if AN, the "AIR" cell is Blank. I hope my explanation was clear enough. If not I will try to clarify further. See formula below =IF(AND(AN183="AIR",AX183="Fri"),AJ183+3,IF(AND(AN 183="AIR",AX183="Sat"),AJ183+2,IF(OR(AN183="AIR",A X183="Mon",AX183="Tue",AX183="Wed",AX183="Thur",AX 183="Sun"),AJ183+1,IF(AX183="Mon",AJ183+4,IF(AX183 ="Tue",AJ183+6,IF(AX183="thu",AJ183+5,IF(AX183="fr i",AJ183+5,IF(AX183="sun",AJ183+5,"")))))))) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help
The easiest way may be to put a table (in A1:C7 or anywhere convenient)
saying something like: Mon 1 4 Tue 1 6 Wed 1 Undefined Thu 1 5 Fri 3 5 Sat 2 Undefined Sun 1 5 (and fill in the undefined gaps as appropriate) then use the formula like: =AJ183+IF(AN183="air",VLOOKUP(AX183,A1:C7,2,FALSE) ,VLOOKUP(AX183,A1:C7,3,FALSE)) You could add additional protection for invalid inputs if you haven't protected against them earlier. -- David Biddulph "DP7" wrote in message ... Hi, I have this formula which is half working. I need it to say first of all If cell AN="AIR" and the AX="Fri" then add 3 days to AJ. if AX = "sat" & AN="air" add 2 days to AJ. Otherwise if AN = "air" and the AX is any of the days not mentioned above add 1 to AJ. After that if AN is blank and AX="mon" add 4 to AJ, if ax ="tue" and 6 to AJ.... so on and so fort. The formula as I have does not pick up the last set of values if AN, the "AIR" cell is Blank. I hope my explanation was clear enough. If not I will try to clarify further. See formula below =IF(AND(AN183="AIR",AX183="Fri"),AJ183+3,IF(AND(AN 183="AIR",AX183="Sat"),AJ183+2,IF(OR(AN183="AIR",A X183="Mon",AX183="Tue",AX183="Wed",AX183="Thur",AX 183="Sun"),AJ183+1,IF(AX183="Mon",AJ183+4,IF(AX183 ="Tue",AJ183+6,IF(AX183="thu",AJ183+5,IF(AX183="fr i",AJ183+5,IF(AX183="sun",AJ183+5,"")))))))) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help
right after the AJ183+2
it should read if(and(AN183="Air",or(AX182="mon",...)),Aj183+1... "DP7" wrote: Hi, I have this formula which is half working. I need it to say first of all If cell AN="AIR" and the AX="Fri" then add 3 days to AJ. if AX = "sat" & AN="air" add 2 days to AJ. Otherwise if AN = "air" and the AX is any of the days not mentioned above add 1 to AJ. After that if AN is blank and AX="mon" add 4 to AJ, if ax ="tue" and 6 to AJ.... so on and so fort. The formula as I have does not pick up the last set of values if AN, the "AIR" cell is Blank. I hope my explanation was clear enough. If not I will try to clarify further. See formula below =IF(AND(AN183="AIR",AX183="Fri"),AJ183+3,IF(AND(AN 183="AIR",AX183="Sat"),AJ183+2,IF(OR(AN183="AIR",A X183="Mon",AX183="Tue",AX183="Wed",AX183="Thur",AX 183="Sun"),AJ183+1,IF(AX183="Mon",AJ183+4,IF(AX183 ="Tue",AJ183+6,IF(AX183="thu",AJ183+5,IF(AX183="fr i",AJ183+5,IF(AX183="sun",AJ183+5,"")))))))) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help
thank you for your suggestion. It is a good one. However i can not do this as
it is a sheet that will be emailed to many people. So the vlookup otion is not one I thin would work for me. That is why I am using if statements. Thanks for your advice though. "David Biddulph" wrote: The easiest way may be to put a table (in A1:C7 or anywhere convenient) saying something like: Mon 1 4 Tue 1 6 Wed 1 Undefined Thu 1 5 Fri 3 5 Sat 2 Undefined Sun 1 5 (and fill in the undefined gaps as appropriate) then use the formula like: =AJ183+IF(AN183="air",VLOOKUP(AX183,A1:C7,2,FALSE) ,VLOOKUP(AX183,A1:C7,3,FALSE)) You could add additional protection for invalid inputs if you haven't protected against them earlier. -- David Biddulph "DP7" wrote in message ... Hi, I have this formula which is half working. I need it to say first of all If cell AN="AIR" and the AX="Fri" then add 3 days to AJ. if AX = "sat" & AN="air" add 2 days to AJ. Otherwise if AN = "air" and the AX is any of the days not mentioned above add 1 to AJ. After that if AN is blank and AX="mon" add 4 to AJ, if ax ="tue" and 6 to AJ.... so on and so fort. The formula as I have does not pick up the last set of values if AN, the "AIR" cell is Blank. I hope my explanation was clear enough. If not I will try to clarify further. See formula below =IF(AND(AN183="AIR",AX183="Fri"),AJ183+3,IF(AND(AN 183="AIR",AX183="Sat"),AJ183+2,IF(OR(AN183="AIR",A X183="Mon",AX183="Tue",AX183="Wed",AX183="Thur",AX 183="Sun"),AJ183+1,IF(AX183="Mon",AJ183+4,IF(AX183 ="Tue",AJ183+6,IF(AX183="thu",AJ183+5,IF(AX183="fr i",AJ183+5,IF(AX183="sun",AJ183+5,"")))))))) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help
You don't need to put it in a table, you can hardcode it in the formula
for instance a table like this a 1 b 2 c 4 d 8 in A1:B4 =VLOOKUP(D2,A1:B4,2,0) would look like this hardcoded =VLOOKUP(D2,{"a ",1;"b",2;"c",4;"d",8},2,0) -- Regards, Peo Sjoblom "DP7" wrote in message ... thank you for your suggestion. It is a good one. However i can not do this as it is a sheet that will be emailed to many people. So the vlookup otion is not one I thin would work for me. That is why I am using if statements. Thanks for your advice though. "David Biddulph" wrote: The easiest way may be to put a table (in A1:C7 or anywhere convenient) saying something like: Mon 1 4 Tue 1 6 Wed 1 Undefined Thu 1 5 Fri 3 5 Sat 2 Undefined Sun 1 5 (and fill in the undefined gaps as appropriate) then use the formula like: =AJ183+IF(AN183="air",VLOOKUP(AX183,A1:C7,2,FALSE) ,VLOOKUP(AX183,A1:C7,3,FALSE)) You could add additional protection for invalid inputs if you haven't protected against them earlier. -- David Biddulph "DP7" wrote in message ... Hi, I have this formula which is half working. I need it to say first of all If cell AN="AIR" and the AX="Fri" then add 3 days to AJ. if AX = "sat" & AN="air" add 2 days to AJ. Otherwise if AN = "air" and the AX is any of the days not mentioned above add 1 to AJ. After that if AN is blank and AX="mon" add 4 to AJ, if ax ="tue" and 6 to AJ.... so on and so fort. The formula as I have does not pick up the last set of values if AN, the "AIR" cell is Blank. I hope my explanation was clear enough. If not I will try to clarify further. See formula below =IF(AND(AN183="AIR",AX183="Fri"),AJ183+3,IF(AND(AN 183="AIR",AX183="Sat"),AJ183+2,IF(OR(AN183="AIR",A X183="Mon",AX183="Tue",AX183="Wed",AX183="Thur",AX 183="Sun"),AJ183+1,IF(AX183="Mon",AJ183+4,IF(AX183 ="Tue",AJ183+6,IF(AX183="thu",AJ183+5,IF(AX183="fr i",AJ183+5,IF(AX183="sun",AJ183+5,"")))))))) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula help
Thank YOU VERY MUCH BJ you solved my problem.
"bj" wrote: right after the AJ183+2 it should read if(and(AN183="Air",or(AX182="mon",...)),Aj183+1... "DP7" wrote: Hi, I have this formula which is half working. I need it to say first of all If cell AN="AIR" and the AX="Fri" then add 3 days to AJ. if AX = "sat" & AN="air" add 2 days to AJ. Otherwise if AN = "air" and the AX is any of the days not mentioned above add 1 to AJ. After that if AN is blank and AX="mon" add 4 to AJ, if ax ="tue" and 6 to AJ.... so on and so fort. The formula as I have does not pick up the last set of values if AN, the "AIR" cell is Blank. I hope my explanation was clear enough. If not I will try to clarify further. See formula below =IF(AND(AN183="AIR",AX183="Fri"),AJ183+3,IF(AND(AN 183="AIR",AX183="Sat"),AJ183+2,IF(OR(AN183="AIR",A X183="Mon",AX183="Tue",AX183="Wed",AX183="Thur",AX 183="Sun"),AJ183+1,IF(AX183="Mon",AJ183+4,IF(AX183 ="Tue",AJ183+6,IF(AX183="thu",AJ183+5,IF(AX183="fr i",AJ183+5,IF(AX183="sun",AJ183+5,"")))))))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|