Why am I getting a #VALUE! Error?

[JeffJ]

Previously I had this formula which was working fine:

=IF(AND(OR($E$5=1,$E$5=2),$E$2="...3/12...",$E$3="
inch"),VLOOKUP($E$6,lookup!$C$3:$D$19,2,0),IF(AND($E$5=3,$E$2="...3/12...",$E$3="
inch"),VLOOKUP($E$6,lookup!$E$3:$F$19,2,0),""))

But after I add another part onto the end of the formula I am no
getting a #VALUE! error (even though, if I enter information in th
referenced cells, it seems to work fine and return the correc
numbers):

=IF(AND(OR($E$5=1,$E$5=2),$E$2="...3/12...",$E$3="
inch"),VLOOKUP($E$6,lookup!$C$3:$D$19,2,0),IF(AND($E$5=3,$E$2="...3/12...",$E$3="
inch"),VLOOKUP($E$6,lookup!$E$3:$F$19,2,0),""))
IF($E$16=$C$33,$E$17,0) + IF($E$18=$C$33,$E$19,0)
IF($E$20=$C$33,$E$21,0) + IF($E$22=$C$33,$E$23,0)
IF($E$24=$C$33,$E$25,0) + IF($E$26=$C$33,$E$27,0)
IF($E$28=$C$33,$E$29,0)

I have several columns of such formulas, and I can use Conditiona
Formatting to hide the #VALUE! error, which is fine.

However, at the far right I have another column that uses this formul
and also returns the #VALUE! error (I assume it's merely reflecting th
#VALUE! error in the other cells):

=SUM(E34:K34)

The problem is, in the SUM formula, Conditional Formatting does no
seem to be able to hide the #VALUE! error (oddly enough).

With both formulas, I have experimented with Ctrl-Shift-Enter to see i
that would get rid of the error (even though I don't intentionally wan
any of the formulas to be arrays), but I still get the #VALUE! error.

Can someone please tell me either:

-Why I am getting the #VALUE! error in the first place, and how t
prevent it;

-OR-

-How to make the #VALUE! error invisible in the SUM formula (like
said, Conditional Formatting does not seem to work, unless I'm doing i
wrong; but Conditional Formula does hide the error with the IF formul
cells).

Thank you

 

[JeffJ]

If I try this:

If I change the formula to the following (enclosing the 2 major part
with IF statements):

=IF(AND(OR($E$5=1,$E$5=2),$E$2="...3/12...",$E$3="6 inch
),VLOOKUP($E$6,lookup!$C$3:$D$19,2,0),IF(AND($E$5=
3,$E$2="...3/12...",$E$3="6 inch"), + IF($E$16=$C$33,$E$17,0)
IF($E$18=$C$33,$E$19,0) + IF($E$20=$C$33,$E$21,0)
IF($E$22=$C$33,$E$23,0) + IF($E$24=$C$33,$E$25,0)
IF($E$26=$C$33,$E$27,0)
IF($E$28=$C$33,$E$29,0),IF(VLOOKUP($E$6,lookup!$E$3:$F$19,2,0),""))
IF($E$16=$C$33,$E$17,0) + IF($E$18=$C$33,$E$19,0)
IF($E$20=$C$33,$E$21,0) + IF($E$22=$C$33,$E$23,0)
IF($E$24=$C$33,$E$25,0) + IF($E$26=$C$33,$E$27,0)
IF($E$28=$C$33,$E$29,0))

Then, if the cell (containing the formula) is empty, it returns #N/
instead of #VALUE!
This is acceptable, because I can hide the #N/A with Conditiona
Formatting in this cell, and Conditional Formatting also works to hid
the #N/A in the SUM formula column (yet it won't hide the #VALUE
error). So far so good. BUT...

If I fill in the referenced cells, so that my IF formula cell no
reflects a number...say 37 for example... then the far-right colum
with the SUM formula...=SUM(E33:K33)....now reflects #VALUE! agai
instead of the 37!!! How can this be? What am I doing wrong?

Thank you for any replies

 

[JeffJ]

Ok, now this is just getting stupid:

In the columns that have the SUM fomulas (actually, one column has SUM
such as: =SUM(E33:K33) ....and the next column has subtractio
formulas such as: =D33-L33 ....

If I highlight all the cells (containing formulas) in both columns an
go to Format>Conditional Formatting, and choose "Formula Is" and clic
on that little button to the far right (that has the tiny red arrow
and select those same cells (so that I get: =$L$31:$M$206 ) and then
change it to read: =ISERROR($L$31:$M$206) and click on Format; the
for Font Color I choose the color of the background.....the #VALUE
errors do NOT disappear!

Yet if I select only 1, or even 4 cells, and do the same thing, th
#VALUE! errors DO disappear! How weird is that!

I guess I'll select a few cells at a time and do Conditiona
Formatting, and at least that will take care of my problem for now.
But if anyone has any answers, please let me know (so I will sto
replying to myself!......LOL)

 

[JeffJ]

Thanks, Mark.

Interestingly, while the following formula returns a #VALUE! error:

=IF(AND(OR(H5=1,H5=2),H2="...3/12...",H3="1
inch"),VLOOKUP(H6,lookup!$C$3:$D$19,2,0),IF(AND(H5=3,H2="...3/12...",H3="1
inch"),VLOOKUP(H6,lookup!$E$3:$F$19,2,0),"")) + IF($H$16=$C$34,$H$17,0
+ IF($H$18=$C$34,$H$19,0) + IF($H$20=$C$34,$H$21,0)
IF($H$22=$C$34,$H$23,0) + IF($H$24=$C$34,$H$25,0)
IF($H$26=$C$34,$H$27,0) + IF($H$28=$C$34,$H$29,0)


This one (from a different cell) returns 0 instead (which i
preferable):

=IF(AND(OR($H$6="38X12",$H$6="40X12",$H$6="42X12",$H$6="44X12",$H$6="46X12",$H$6="48X12",$H$6="50X12",$H$6="52X12",$H$6="54X12",$H$6="56X12",$H$6="58X12",$H$6="60X12",$H$6="62X12",$H$6="64X12",$H$6="66X12",$H$6="68X12",$H$6="70X12"),$H$2="...3/12...",$H$3="1
inch"),COUNTIF($H$7:$H$16,"=E-120-D")) + IF($H$16=$C$36,$H$17,0)
IF($H$18=$C$36,$H$19,0) + IF($H$20=$C$36,$H$21,0)
IF($H$22=$C$36,$H$23,0) + IF($H$24=$C$36,$H$25,0)
IF($H$26=$C$36,$H$27,0) + IF($H$28=$C$36,$H$29,0)

Not sure why that is, but I see that my first formula contains VLOOKUP
whereas the second does not.

Note that I only get #VALUE! or 0 if the formula cells are blank.

Anyway, thanks for your reply.

Jeff Jenkins
Ocala, FL (SO happy I moved away from Miami!)
(e-mail address removed)
(e-mail address removed)
http://lightningfingers.tripod.co

 

[JeffJ]

Mark,

Cool, I didn't even realize that you could apply relative references t
Conditional Formatting like that (duh!); though I knew you could wit
regular formulas. I learned something new. Thanks for that!

Thank you for the offer to look at the workbook. All the errors ar
invisible now, which works fine for me. But if you are really curious
I will e-mail you the workbook if you want...let me know. Though it'
only 384k in size, it has 206 rows and 13 columns on Sheet1. Sheets 2
3 and 4 contain references and a lot of notes and examples (since th
final user will be someone else who is not very computer-literate, an
the form may possibly be periodically updated by some hired programme
in the future, so I'm trying to make it easy and as self-explanatory a
possible for them). It's not completed yet, but most of the formula
are lengthy just as in my examples.

(e-mail address removed)
http://lightningfingers.tripod.co

 

[Guest]

Hi Jeff
Glad to help you out. Sometimes it's the little things we don't know that make life a lot easier. If you don't mind sending me the file, I am curious what you are doing with these formulas. I might even be able to shorten a few for you

Regards
Mark Graesse
(e-mail address removed)
Boston M

----- JeffJ > wrote: ----

Mark

Cool, I didn't even realize that you could apply relative references t
Conditional Formatting like that (duh!); though I knew you could wit
regular formulas. I learned something new. Thanks for that

Thank you for the offer to look at the workbook. All the errors ar
invisible now, which works fine for me. But if you are really curious
I will e-mail you the workbook if you want...let me know. Though it'
only 384k in size, it has 206 rows and 13 columns on Sheet1. Sheets 2
3 and 4 contain references and a lot of notes and examples (since th
final user will be someone else who is not very computer-literate, an
the form may possibly be periodically updated by some hired programme
in the future, so I'm trying to make it easy and as self-explanatory a
possible for them). It's not completed yet, but most of the formula
are lengthy just as in my examples

(e-mail address removed)
http://lightningfingers.tripod.co

 

[JeffJ]

Mark,

I have just now e-mailed you the Excel 2000 file from my AOL e-mail.
Let me know if you do not receive it.

Jeff

(e-mail address removed)
http://lightningfingers.tripod.co