SUMS with Blanks and Zeros

  • Thread starter Thread starter Josh W.
  • Start date Start date
J

Josh W.

When I'm using the SUM function for a template, it adds all the BLANKS,
counting them as ZERO. This is throwing off my AVERAGES, since I
want BLANK cells to remain blank.

What do I have to do so that BLANKS do not count as ZERO?

Thanks
 
Hi
if you really have blank cells neither SUM nor AVERAGE should be
affected. e.g. use
=AVERAGE(A1:A100)
AVERAGE will skip real blank cells.
 
One way:

=IF(SUM(M7:M17),SUM(M7:M17),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Thanks for the reply Frank.

I've double-checked, and tried the simple formula =SUM(M7:M17), and
although they are all BLANK, the SUM comes to Zero. So, maybe I have
asked the wrong question. I want the SUM to be BLANK (instead of
ZERO) if the values of the cells are BLANK.

Any insights?
 
Thanks for the reply Frank.

I've double-checked, and tried the simple formula =SUM(M7:M17), and
although they are all BLANK, the SUM comes to Zero. So, maybe I have
asked the wrong question. I want the SUM to be BLANK (instead of
ZERO) if the values of the cells are BLANK.

Any insights?
 
Are you summing certain ranges and then average the results of the ranges?

2 5
4 5
6 5
---- --- ---
12 15 0

then average

=(12,15,0)

?

Then you could use

=IF(COUNTBLANK(A1:A10)=10,"",SUM(A1:A10))

don't know what you are trying to get with it though?




--

Regards,

Peo Sjoblom

Thanks for the reply Frank.

I've double-checked, and tried the simple formula =SUM(M7:M17), and
although they are all BLANK, the SUM comes to Zero. So, maybe I have
asked the wrong question. I want the SUM to be BLANK (instead of
ZERO) if the values of the cells are BLANK.

Any insights?
Click to expand...
 
Brilliant! This works!

Is there some similar conditional for calculating AVERAGES only if there
is imput? I used the formula you supplied but replaced SUM with
AVERAGE and it still came out with # # # #.

Thanks!
 
One way:

=IF(ISERR(AVERAGE(M7:M17)),"",AVERAGE(M7:M17))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Brilliant! This works!

Is there some similar conditional for calculating AVERAGES only if there
is imput? I used the formula you supplied but replaced SUM with
AVERAGE and it still came out with # # # #.

Thanks!
 
OR, to copy the first formula:

=IF(SUM(M7:M17),AVERAGE(M7:M17),"")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


One way:

=IF(ISERR(AVERAGE(M7:M17)),"",AVERAGE(M7:M17))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Brilliant! This works!

Is there some similar conditional for calculating AVERAGES only if there
is imput? I used the formula you supplied but replaced SUM with
AVERAGE and it still came out with # # # #.

Thanks!
 
You are THE MAN! Seriously, this works PERFECT! Thanks so
much...there's NO WAY I would ever figure that out on my own.

Blessings,
Josh
 
Glad to help.
Thanks for the feed-back.
--


Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit!
-------------------------------------------------------------------

You are THE MAN! Seriously, this works PERFECT! Thanks so
much...there's NO WAY I would ever figure that out on my own.

Blessings,
Josh
 
Hi
just as an addition. I'd use Peo's solution (using COUNTBLANK for
checking the range) as this formula will give a wrong result if the
values in the range M7:M17 will add up to zero. e.g.
M7: 5
M8: -5
all other values blank
In this case a blank cell is shown as result and not a zero. So
combining your further response with Peo's solution I'd use
=IF(COUNTBLANK(M7:M17)=10,"",AVERAGE(M7:M17))
 
RagDyer said:
OR, to copy the first formula:

=IF(SUM(M7:M17),AVERAGE(M7:M17),"")
Click to expand...
....

Or to reduce this to its basics,

=IF(COUNT(M7:M17),AVERAGE(M7:M17),"")

Note that SUM as criteria would produce "" if M7:M17 contained numbers that
happen to sum to zero. Maybe the OP doesn't want to hide legitimate zero
averages, but maybe not.
 
Josh said:
*When I'm using the SUM function for a template, it adds all th
BLANKS,
counting them as ZERO. This is throwing off my AVERAGES, since I
want BLANK cells to remain blank.

What do I have to do so that BLANKS do not count as ZERO?

Thanks *
Click to expand...

I have a similar problem. I am trying to create a blank invoice for
college assignment. I have worked out the sum for QTY multiplied bu
UNIT PRICE which gives a figure and leaves blank if nothing entered bu
I want to work out a sub total at the bottom of the screen (e.g
D12:D20) so I can add VAT.
I am not sure what the SUM or IF statement will be so any help would b
very much appreciated

Thank
 
Back
Top