Help - Please If, And = #Value!

[Iona]

Hello,

I was wondering if someone can help me figure out why I receive the error
message "#VALUE!" with the formula below. I am evaluating dates. Thank you.

=IF(AND($D:$D<100%,$K:$K>TODAY()),"Review
Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind
Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Completed
Late",""),IF(AND($D:$D=100%,$K:$K>$J:$J),"Completed Ahead of Schedule","")

 

[Peo Sjoblom]

Tell us what you want to do instead of posting the formula.
AND when used this way need to compare single cells and even if it did work
you can't use the whole column (D) in array formulas. To check multiple
cells you would need COUNTIF or SUMPRODUCT but you would be better off if
you post exactly what you want to do

--


Regards,


Peo Sjoblom

 

[Iona]

Thanks Peo for the feedback. This is my first submission, the direction was
needed. I am evaluating two cell ranges.

Example: D12 = 100% and K12 = 09/11/08
=IF(AND($D:$D<100%,$K:$K>TODAY()),"Review Only","")

I am seeking a formula which will return various statements if the 2
conditions are met. The formula above is correct. It is just when I add
multiple conditions I am having the problem.
=IF(AND($D:$D<100%,$K:$K>TODAY()),"Review
Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind
Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Completed
Late",""),IF(AND($D:$D=100%,$K:$K>$J:$J),"Completed Ahead of Schedule","")

 

[Mike H]

Hi,

I think you want this dragged down

=IF(AND($D1<100%,$K1>TODAY()),"Review
Only",IF(AND($D1<100%,$K1<TODAY()),"Behind
Schedule",IF(AND($D1=100%,$K1<$J1),"Completed
Late",IF(AND($D1=100%,$K1>$J1),"Completed Ahead of Schedule",""))))

Mike

 

[Peo Sjoblom]

Assume your data starts in D2, adapt to fit for your first cell with data
that you want to evaluate, in (preferably)
an adjacent cell put


=IF(OR($D2="",$K2=""),"",IF(AND($D2<100%,$K2>TODAY()),"Review
Only",IF(AND($D2<100%,$K2<TODAY()),"Behind
Schedule",IF(AND($D2=100%,$K2<$J2),"Completed
Late",IF(AND($D2=100%,$K2>$J2),"Completed Ahead of Schedule","")))))



Then grab the lower right corner of the cell with the formula and copy it
down as long as needed

--


Regards,


Peo Sjoblom

 

[Iona]

Thank you! Thank you! Whew...

 

[Peo Sjoblom]

Just a heads up, if the cells are empty your formula will return

"Behind Schedule"



--


Regards,


Peo Sjoblom

 

[Iona]

Thank you, that was helpful too!

Assume your data starts in D2, adapt to fit for your first cell with data
that you want to evaluate, in (preferably)
an adjacent cell put


=IF(OR($D2="",$K2=""),"",IF(AND($D2<100%,$K2>TODAY()),"Review
Only",IF(AND($D2<100%,$K2<TODAY()),"Behind
Schedule",IF(AND($D2=100%,$K2<$J2),"Completed
Late",IF(AND($D2=100%,$K2>$J2),"Completed Ahead of Schedule","")))))



Then grab the lower right corner of the cell with the formula and copy it
down as long as needed

--


Regards,


Peo Sjoblom

 

[Mike H]

Thanks Peo, I never checked the outputs of the formula ( I should have) I
simply corrected the syntax.

Mike