Want #VALUE! to show as blank cell
 

Hi,

Using Windows 2000 Pro, Excel 2003.

Im 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 months meter reading. Cell C7 has the current
months reading. The formulas Im 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 dont 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 months 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(D720000,D7-20000,"")
F =IF(E7<=0,"",E7*0.015)

Thanks.

Want #VALUE! to show as blank cell
 

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

"JoeyW" wrote in message
...
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(D720000,D7-20000,"")
F =IF(E7<=0,"",E7*0.015)

Thanks.

Want #VALUE! to show as blank cell
 

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

If you don't want to see zero, suppress them, ToolsOptionsGeneralView and
uncheck Zero Values

--

HTH

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


"JoeyW" wrote in message
...
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(D720000,D7-20000,"")
F =IF(E7<=0,"",E7*0.015)

Thanks.