Want #VALUE! to show as blank cell

[Guest]

Hi,

Using Windows 2000 Pro, Excel 2003.

I’m tracking the amount of copies our office uses on a monthly basis. If we
go over 20,000 copies, we have to pay an additional charge for those copies.
Cell B7 has the previous month’s meter reading. Cell C7 has the current
month’s reading. The formulas I’m using work, except when we do not go over
the 20,000, I end up with #VALUE! in Cell F8. I assume this is because Cell
E8 is not actually blank, but has a formula in it?? If a cell has no value, I
want it to show as empty, but I don’t know how to accomplish this for Cell
F8, and E9 & F9/10, which are in cells on rows for months in the future (I
want to set this up for an entire year so all I have to enter each month is
the Current month’s reading).

I hope this makes sense. What do I need to do in order to get the cells with
#VALUE! to show as blank cells?

The following are examples of the cells and formulas:

B C D E F
Past Current Used Over Pay This
7 20,000 50,000 30,000 10,000 $150.00
8 50,000 65,500 15,500 #VALUE!
9 65,500 #VALUE! #VALUE!
10 #VALUE!

The formula in columns D, E, & F:

B8 =IF(C7="","",SUM(C7))

D =IF(C7="","",SUM(C7-B7))
E =IF(D7>20000,D7-20000,"")
F =IF(E7<=0,"",E7*0.015)

Thanks.

 

[Roy P]

Work the "iserr funtion into your formulas as listed.
B8 =if(iserr(IF(C7="","",SUM(C7)))=true,"",IF(C7="","",SUM(C7)))
best of luck.

 

[Bob Phillips]

In all of your formula, change the "" to 0.

If you don't want to see zero, suppress them, Tools>Options>General>View and
uncheck Zero Values

--

HTH

RP
(remove nothere from the email address if mailing direct)