#VALUE problem

[Dave Potter]

Given the formulas in the cell locations at the left:


B8=IF(ISNA(VLOOKUP($A8,MON!$A$11:$C$29,1,FALSE)),"",VLOOKUP($A8,MON!$A$11:$C$29,2,FALSE))
C8=IF(ISNA(VLOOKUP($A8,MON!$A$11:$C$29,1,FALSE)),"",VLOOKUP($A8,MON!$A$11:$C$29,3,FALSE))
D8=B8
E8=C8
F8=IF(ISBLANK($A8),"",(E8-D8)*24)
G8=IF(ISBLANK($A8),"",((E8-D8)+1)*24)
H8=D8
I8=IF(ISBLANK($A8),"",IF(E8<0.2,(E8+1),E8)-IF(D8<0.1,(E8+1)))
J8=IF(F8<0,G8,F8)


When A8 contains a persons name but the name is not found in the
lookup range, #VALUE is returned in F8, G8, I8, and J8. At the bottom
each of those columns I need the sum of those coulmn values. (ie
=Sum(F8:F53). How might I change the formula in F8, G8, I8, and J8 so
that I can get rid of the #VALUE error and get a correct sum of those
columns? I've tried various options such as the IF(ISBLANK(blah),
blah, blah) but it isn't working

Thank you
David

 

[Harlan Grove]

F8=IF(ISBLANK($A8),"",(E8-D8)*24)
G8=IF(ISBLANK($A8),"",((E8-D8)+1)*24)

....

Change these to

F8: =IF(AND($A8<>"",D8<>""),(E8-D8)*24,"")

I8=IF(ISBLANK($A8),"",IF(E8<0.2,(E8+1),E8)-IF(D8<0.1,(E8+1)))

....

Change this to

I8: =IF(AND($A8<>"",D8<>""),E8+(E8<0.2)-(D8<0.1)*(E8+1),"")

 

[Biff]

Hi Dave,

At first glance and without testing If there is a name
entered in A8 but no match is found the lookup returns a
null string "". The null string is then propagated to D8
via B8 which contains the lookup. Then in F8 the reference
to D8 is to the null string causing the error.

You could try something like:

F8=IF(OR(ISBLANK($A8),D8=""),"",(E8-D8)*24)

Biff

 

[Dave Potter]

Harlan,

Thank you very much!! That works great. If you have the time could you
explain that change in laymans terms to a relatively new user?
I like knowing what I did wrong.
Thanks again
David

 

[Harlan Grove]

Thank you very much!! That works great. If you have the time could you
explain that change in laymans terms to a relatively new user?
I like knowing what I did wrong.

....

You were only checking if the A8 cell was blank. Your B8:E8 cells would also
be blank if there were no match for A8 when A8 wasn't blank, so you needed
to check that any of those cells wasn't blank in order to ensure that the
arithmetic calculations in the cells farther right were valid.

The problem was that when A8 wasn't blank but matched nothing in the table,
D8 and E8 would be (pseudo)blank, "", which is actually text that can't be
converted into numbers. So E8-D8 would be the same as ""-"", which throws a
#VALUE! error.