IF(AND(OR function returns #VALUE! error
 

Hi,

The following formula in a cell retuns the #VALUE! error message. I have
tried using the formula auditing-evaluate formula. It shows the #VALUE!
message when it gets to the part about "ProcAP", but I do not understand why.
So I broke down the formula into the separate pieces and tested each part,
they all work independantly just fine. I am missing somthing, and would
appreciate any help you could offer.

The needed result is to look at a code in column B and based on a date in
either column Z or column AA determine if it is older than 30 days or 90 days
from todays date and either return what is in column B or add the text from
column B plus " <3 Months" or " < 1 Month".

The actual formula's are typed below:

Combined Formula:
=IF(AND(B3="PCard",Z3<(TODAY()-90)),B3,B3&" < 3
Months"),IF(AND(B3="ProcPUR",Z3<(TODAY()-90)),B3,B3&" < 3
Months"),IF(AND(B3="ProcAP",AA3<(TODAY()-30)),B3,B3&" < 1
Month"),IF(OR(B3="REC'ving",B3="FSC"),B3,"")

Separated parts of Formula:
=IF(AND(B3="PCard",Z3<(TODAY()-90)),"PCard",B3&" < 3 Months")
=IF(AND(B3="ProcPUR",Z3<(TODAY()-90)),"ProcPUR",B3&" < 3 Months")
=IF(AND(B3="ProcAP",AA3<(TODAY()-30)),B3,B3&" < 1 Month")
=IF(OR(B3="REC'ving",B3="FSC"),B3,"")

Column 3 contains either PCard, REC'ving, ProcPUR, ProcAP, or FSC
Column Z contains an Order Date ( approx 5 yr range through today's date)
Column AA contains a Received Date (approx 5 yr range through today's date)

Thank you for any solutions or guidance you can offer!

Linda

IF(AND(OR function returns #VALUE! error
 

You can't just combine independent, complete IF formulas by concatenating
them with a comma in between each. Each successive IF statement needs to be
the True or False result from the previous IF. From your description, it
sounds like you never want it to return "". Try this:

=IF(AND(OR(B3="PCard",B3="ProcPUR"),Z3=(TODAY()-90)),B3&" < 3
Months",IF(AND(B3="ProcAP",AA3=(TODAY()-30)),B3&" < 1 Month",B3))

Hope this helps,

Hutch

"Linda" wrote:

Hi,

The following formula in a cell retuns the #VALUE! error message. I have
tried using the formula auditing-evaluate formula. It shows the #VALUE!
message when it gets to the part about "ProcAP", but I do not understand why.
So I broke down the formula into the separate pieces and tested each part,
they all work independantly just fine. I am missing somthing, and would
appreciate any help you could offer.

The needed result is to look at a code in column B and based on a date in
either column Z or column AA determine if it is older than 30 days or 90 days
from todays date and either return what is in column B or add the text from
column B plus " <3 Months" or " < 1 Month".

The actual formula's are typed below:

Combined Formula:
=IF(AND(B3="PCard",Z3<(TODAY()-90)),B3,B3&" < 3
Months"),IF(AND(B3="ProcPUR",Z3<(TODAY()-90)),B3,B3&" < 3
Months"),IF(AND(B3="ProcAP",AA3<(TODAY()-30)),B3,B3&" < 1
Month"),IF(OR(B3="REC'ving",B3="FSC"),B3,"")

Separated parts of Formula:
=IF(AND(B3="PCard",Z3<(TODAY()-90)),"PCard",B3&" < 3 Months")
=IF(AND(B3="ProcPUR",Z3<(TODAY()-90)),"ProcPUR",B3&" < 3 Months")
=IF(AND(B3="ProcAP",AA3<(TODAY()-30)),B3,B3&" < 1 Month")
=IF(OR(B3="REC'ving",B3="FSC"),B3,"")

Column 3 contains either PCard, REC'ving, ProcPUR, ProcAP, or FSC
Column Z contains an Order Date ( approx 5 yr range through today's date)
Column AA contains a Received Date (approx 5 yr range through today's date)

Thank you for any solutions or guidance you can offer!

Linda

IF(AND(OR function returns #VALUE! error
 

You included too many FALSE options. Your first IF statement returns either
B3, or B3 and text. You've left no opening now for any other formula. You
then placed a comma and jump right into next function. You can't do that! You
need to figure out what each option is for your true and false conditions.

When you decide what path you want each function to take, you'll be able to
better nest your functions, or describe better what you want, and someone
here will probably be able to help.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Linda" wrote:

Hi,

The following formula in a cell retuns the #VALUE! error message. I have
tried using the formula auditing-evaluate formula. It shows the #VALUE!
message when it gets to the part about "ProcAP", but I do not understand why.
So I broke down the formula into the separate pieces and tested each part,
they all work independantly just fine. I am missing somthing, and would
appreciate any help you could offer.

The needed result is to look at a code in column B and based on a date in
either column Z or column AA determine if it is older than 30 days or 90 days
from todays date and either return what is in column B or add the text from
column B plus " <3 Months" or " < 1 Month".

The actual formula's are typed below:

Combined Formula:
=IF(AND(B3="PCard",Z3<(TODAY()-90)),B3,B3&" < 3
Months"),IF(AND(B3="ProcPUR",Z3<(TODAY()-90)),B3,B3&" < 3
Months"),IF(AND(B3="ProcAP",AA3<(TODAY()-30)),B3,B3&" < 1
Month"),IF(OR(B3="REC'ving",B3="FSC"),B3,"")

Separated parts of Formula:
=IF(AND(B3="PCard",Z3<(TODAY()-90)),"PCard",B3&" < 3 Months")
=IF(AND(B3="ProcPUR",Z3<(TODAY()-90)),"ProcPUR",B3&" < 3 Months")
=IF(AND(B3="ProcAP",AA3<(TODAY()-30)),B3,B3&" < 1 Month")
=IF(OR(B3="REC'ving",B3="FSC"),B3,"")

Column 3 contains either PCard, REC'ving, ProcPUR, ProcAP, or FSC
Column Z contains an Order Date ( approx 5 yr range through today's date)
Column AA contains a Received Date (approx 5 yr range through today's date)

Thank you for any solutions or guidance you can offer!

Linda

IF(AND(OR function returns #VALUE! error
 

Thank you Hutch,

I should have know better, the minute I read your reply it made perfect
sense. Once I entered the first false statement, I was done.

Thanks again!
Linda

"Tom Hutchins" wrote:

You can't just combine independent, complete IF formulas by concatenating
them with a comma in between each. Each successive IF statement needs to be
the True or False result from the previous IF. From your description, it
sounds like you never want it to return "". Try this:

=IF(AND(OR(B3="PCard",B3="ProcPUR"),Z3=(TODAY()-90)),B3&" < 3
Months",IF(AND(B3="ProcAP",AA3=(TODAY()-30)),B3&" < 1 Month",B3))

Hope this helps,

Hutch

"Linda" wrote:

Hi,

The following formula in a cell retuns the #VALUE! error message. I have
tried using the formula auditing-evaluate formula. It shows the #VALUE!
message when it gets to the part about "ProcAP", but I do not understand why.
So I broke down the formula into the separate pieces and tested each part,
they all work independantly just fine. I am missing somthing, and would
appreciate any help you could offer.

The needed result is to look at a code in column B and based on a date in
either column Z or column AA determine if it is older than 30 days or 90 days
from todays date and either return what is in column B or add the text from
column B plus " <3 Months" or " < 1 Month".

The actual formula's are typed below:

Combined Formula:
=IF(AND(B3="PCard",Z3<(TODAY()-90)),B3,B3&" < 3
Months"),IF(AND(B3="ProcPUR",Z3<(TODAY()-90)),B3,B3&" < 3
Months"),IF(AND(B3="ProcAP",AA3<(TODAY()-30)),B3,B3&" < 1
Month"),IF(OR(B3="REC'ving",B3="FSC"),B3,"")

Separated parts of Formula:
=IF(AND(B3="PCard",Z3<(TODAY()-90)),"PCard",B3&" < 3 Months")
=IF(AND(B3="ProcPUR",Z3<(TODAY()-90)),"ProcPUR",B3&" < 3 Months")
=IF(AND(B3="ProcAP",AA3<(TODAY()-30)),B3,B3&" < 1 Month")
=IF(OR(B3="REC'ving",B3="FSC"),B3,"")

Column 3 contains either PCard, REC'ving, ProcPUR, ProcAP, or FSC
Column Z contains an Order Date ( approx 5 yr range through today's date)
Column AA contains a Received Date (approx 5 yr range through today's date)

Thank you for any solutions or guidance you can offer!

Linda