SUMS with Blanks and Zeros

[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

 

[Frank Kabel]

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.

 

[RagDyer]

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?

 

[Guest]

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?

 

[Peo Sjoblom]

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?

 

[Josh W.]

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!

 

[RagDyer]

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!

 

[RagDyer]

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!

 

[Josh W.]

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

 

[RagDyer]

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

 

[Frank Kabel]

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))

 

[Harlan Grove]

OR, to copy the first formula:

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

....

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 W.]

Thanks Frank for the help! I figured it out! Blessings!

 

[AdRock]

*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 *

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
D1220) 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