Function is suppress #VALUE! ?

  • Thread starter Thread starter scottwilsonx
  • Start date Start date
S

scottwilsonx

Hope someone can help.
I have a number of cells which individually add up to sub-totals.
Where the cells they are totally equal 0, then the formula forces
"-". This is more for asthetic appeal than anything else.

Anyway, I have a Total which then adds up the sub-totals. This is al
very good, until one of the sub-totals remains at "-" as there is n
data to sub-total. When this happens I get a #VALUE! returned.

Is there a function I can use to ensure that where one of th
sub-totals says "-" it is ignored from the formula ?

Many thanks for your help/

Scott
 
Hi Scott

rather than use a formula to force a "-" you can use custom formatting
(format / cells - numbers tab, custom) - which (AFAIK) will mean that you
won't get the #VALUE when you total.

One of the options under custom formatting that displays a 0 as a - is
_-* #,##0_-;-* #,##0_-;_-* "-"_-;_-@_-

but there are others there - depending on how you want postive / negative /
text to look if it ends up in the formatted cell.
Hope this helps
Cheers
JulieD
 
Julie. thanks for the reply.
I would much rather use a formula in the cell. Is this possible ?
Sorry to be a pain!

Scott
 
Hi Scott

what is the formula that you're using to display zero as "-" and what is the
function you're using to get your totals?

Cheers
JulieD
 
Hi Julie,

The formula for the "-" is:
=IF(SUM(B14:G14)<1,"-",SUM(B14:G14))
Where B14:G14 is the array.

The formula for the total is very simply:
=SUM(I45+I37+I28+I14)
Where I45 etc are the sub-totals mentioned above.

Many thanks for your help.
Scott.
 
Back
Top