| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help - Please If, And = #Value!
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:$KTODAY()),"Review Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Comp leted Late",""),IF(AND($D:$D=100%,$K:$K$J:$J),"Complete d Ahead of Schedule","") |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help - Please If, And = #Value!
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: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" wrote in message ... 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:$KTODAY()),"Review Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Comp leted Late",""),IF(AND($D:$D=100%,$K:$K$J:$J),"Complete d Ahead of Schedule","") |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help - Please If, And = #Value!
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:$KTODAY()),"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:$KTODAY()),"Review Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Comp leted Late",""),IF(AND($D:$D=100%,$K:$K$J:$J),"Complete d Ahead of Schedule","") "Iona" wrote: 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:$KTODAY()),"Review Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Comp leted Late",""),IF(AND($D:$D=100%,$K:$K$J:$J),"Complete d Ahead of Schedule","") |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help - Please If, And = #Value!
Hi,
I think you want this dragged down =IF(AND($D1<100%,$K1TODAY()),"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 "Iona" wrote: 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:$KTODAY()),"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:$KTODAY()),"Review Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Comp leted Late",""),IF(AND($D:$D=100%,$K:$K$J:$J),"Complete d Ahead of Schedule","") "Iona" wrote: 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:$KTODAY()),"Review Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Comp leted Late",""),IF(AND($D:$D=100%,$K:$K$J:$J),"Complete d Ahead of Schedule","") |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help - Please If, And = #Value!
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%,$K2TODAY ()),"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" wrote in message ... 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:$KTODAY()),"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:$KTODAY()),"Review Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Comp leted Late",""),IF(AND($D:$D=100%,$K:$K$J:$J),"Complete d Ahead of Schedule","") "Iona" wrote: 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:$KTODAY()),"Review Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Comp leted Late",""),IF(AND($D:$D=100%,$K:$K$J:$J),"Complete d Ahead of Schedule","") |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help - Please If, And = #Value!
Thank you! Thank you! Whew...
"Iona" wrote: 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:$KTODAY()),"Review Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Comp leted Late",""),IF(AND($D:$D=100%,$K:$K$J:$J),"Complete d Ahead of Schedule","") |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help - Please If, And = #Value!
Just a heads up, if the cells are empty your formula will return
"Behind Schedule" -- Regards, Peo Sjoblom "Mike H" wrote in message ... Hi, I think you want this dragged down =IF(AND($D1<100%,$K1TODAY()),"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 "Iona" wrote: 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:$KTODAY()),"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:$KTODAY()),"Review Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Comp leted Late",""),IF(AND($D:$D=100%,$K:$K$J:$J),"Complete d Ahead of Schedule","") "Iona" wrote: 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:$KTODAY()),"Review Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Comp leted Late",""),IF(AND($D:$D=100%,$K:$K$J:$J),"Complete d Ahead of Schedule","") |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help - Please If, And = #Value!
Thank you, that was helpful too!
"Peo Sjoblom" wrote: 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%,$K2TODAY ()),"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" wrote in message ... 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:$KTODAY()),"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:$KTODAY()),"Review Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Comp leted Late",""),IF(AND($D:$D=100%,$K:$K$J:$J),"Complete d Ahead of Schedule","") "Iona" wrote: 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:$KTODAY()),"Review Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Comp leted Late",""),IF(AND($D:$D=100%,$K:$K$J:$J),"Complete d Ahead of Schedule","") |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Help - Please If, And = #Value!
Thanks Peo, I never checked the outputs of the formula ( I should have) I
simply corrected the syntax. Mike "Peo Sjoblom" wrote: 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%,$K2TODAY ()),"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" wrote in message ... 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:$KTODAY()),"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:$KTODAY()),"Review Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Comp leted Late",""),IF(AND($D:$D=100%,$K:$K$J:$J),"Complete d Ahead of Schedule","") "Iona" wrote: 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:$KTODAY()),"Review Only",""),IF(AND($D:$D<100%,$K:$K<TODAY()),"Behind Schedule",""),IF(AND($D:$D=100%,$K:$K<$J:$J),"Comp leted Late",""),IF(AND($D:$D=100%,$K:$K$J:$J),"Complete d Ahead of Schedule","") |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
