Formula help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DP7 - 11 Sep 2007 14:38 GMT

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(AN183="AIR",AX183="Sat"),AJ183+2,IF(OR(AN183="AIR",AX183="Mon",AX183="Tue",AX183="Wed",AX183="Thur",AX183="Sun"),AJ183+1,IF(AX183="Mon",AJ183+4,IF(AX183="Tue",AJ183+6,IF(AX183="thu",AJ183+5,IF(AX183="fri",AJ183+5,IF(AX183="sun",AJ183+5,""))))))))

Reply to this Message

David Biddulph - 11 Sep 2007 15:18 GMT

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.

Signature

David Biddulph

> Hi, I have this formula which is half working. I need it to say first of
> all
[quoted text clipped - 11 lines]
>
> =IF(AND(AN183="AIR",AX183="Fri"),AJ183+3,IF(AND(AN183="AIR",AX183="Sat"),AJ183+2,IF(OR(AN183="AIR",AX183="Mon",AX183="Tue",AX183="Wed",AX183="Thur",AX183="Sun"),AJ183+1,IF(AX183="Mon",AJ183+4,IF(AX183="Tue",AJ183+6,IF(AX183="thu",AJ183+5,IF(AX183="fri",AJ183+5,IF(AX183="sun",AJ183+5,""))))))))

Reply to this Message

DP7 - 11 Sep 2007 15:30 GMT

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.

> The easiest way may be to put a table (in A1:C7 or anywhere convenient)
> saying something like:
[quoted text clipped - 27 lines]
> >
> > =IF(AND(AN183="AIR",AX183="Fri"),AJ183+3,IF(AND(AN183="AIR",AX183="Sat"),AJ183+2,IF(OR(AN183="AIR",AX183="Mon",AX183="Tue",AX183="Wed",AX183="Thur",AX183="Sun"),AJ183+1,IF(AX183="Mon",AJ183+4,IF(AX183="Tue",AJ183+6,IF(AX183="thu",AJ183+5,IF(AX183="fri",AJ183+5,IF(AX183="sun",AJ183+5,""))))))))

Reply to this Message

Peo Sjoblom - 11 Sep 2007 15:38 GMT

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)

Signature

Regards,

Peo Sjoblom

> thank you for your suggestion. It is a good one. However i can not do this
> as
[quoted text clipped - 36 lines]
>> >
>> > =IF(AND(AN183="AIR",AX183="Fri"),AJ183+3,IF(AND(AN183="AIR",AX183="Sat"),AJ183+2,IF(OR(AN183="AIR",AX183="Mon",AX183="Tue",AX183="Wed",AX183="Thur",AX183="Sun"),AJ183+1,IF(AX183="Mon",AJ183+4,IF(AX183="Tue",AJ183+6,IF(AX183="thu",AJ183+5,IF(AX183="fri",AJ183+5,IF(AX183="sun",AJ183+5,""))))))))

Reply to this Message

bj - 11 Sep 2007 15:26 GMT

right after the AJ183+2
it should read
if(and(AN183="Air",or(AX182="mon",...)),Aj183+1...

> 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" &
[quoted text clipped - 6 lines]
>
> =IF(AND(AN183="AIR",AX183="Fri"),AJ183+3,IF(AND(AN183="AIR",AX183="Sat"),AJ183+2,IF(OR(AN183="AIR",AX183="Mon",AX183="Tue",AX183="Wed",AX183="Thur",AX183="Sun"),AJ183+1,IF(AX183="Mon",AJ183+4,IF(AX183="Tue",AJ183+6,IF(AX183="thu",AJ183+5,IF(AX183="fri",AJ183+5,IF(AX183="sun",AJ183+5,""))))))))

Reply to this Message

DP7 - 11 Sep 2007 15:46 GMT

Thank YOU VERY MUCH BJ you solved my problem.

> right after the AJ183+2
> it should read
[quoted text clipped - 10 lines]
> >
> > =IF(AND(AN183="AIR",AX183="Fri"),AJ183+3,IF(AND(AN183="AIR",AX183="Sat"),AJ183+2,IF(OR(AN183="AIR",AX183="Mon",AX183="Tue",AX183="Wed",AX183="Thur",AX183="Sun"),AJ183+1,IF(AX183="Mon",AJ183+4,IF(AX183="Tue",AJ183+6,IF(AX183="thu",AJ183+5,IF(AX183="fri",AJ183+5,IF(AX183="sun",AJ183+5,""))))))))

Reply to this Message