Average

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

I have a spreadsheet with the months January thru December(B3:M3). I'd
like the average usage but my months are only from January thru June
for now. How to you write the formula to include all months and still
only average the ones that have been used so far. =AVERAGE(B3:M3)
averages all months. I need it to average January to the current
month. Thanks in Advance!
 
AVERAGE function ignores blank cells, text cells and cells with formulas that
return ""

What do you have in the unused months that AVERAGE will not ignore?


Gord Dibben MS Excel MVP
 
AVERAGE function ignores blank cells, text cells and cells with formulas that
return ""

What do you have in the unused months that AVERAGE will not ignore?

Gord Dibben     MS Excel MVP





- Show quoted text -
Click to expand...

If I use =Average(B3:M3) Which is January thru December it will divide
by 12 which is fine if I'm in the month of December. But... If I'm in
the month of June I only want it to divide by 6, If I'm in the month
of July I want it to divide by 7 and so on.... I think this might
work?
=IF(N3=0,"",SUM(B3,C3,D3,E3,F3,G3,H3,I3,J3,K3,L3,M3)/
SUM(COUNTIF(INDIRECT({"B3","C3","D3","E3","F3","G3","H3","I3","J3","K3","L3","M3"}),">0")))
Not sure yet! Thanks for your help!!!
 
AVERAGE function ignores blank cells, text cells and cells
with formulas that return ""
Click to expand...

..... Which is text ;-).
What do you have in the unused months that AVERAGE will
not ignore?
Click to expand...

And what version of Excel do you have?

If you have XL2007 or later, you might be able to use AVERAGEIF
instead of an array formula of the form AVERAGE(IF(B3:M3<>...,B3:M3)).
 
How about doing your own average. Something like..

=SUM(B3:M3)/COUNTA(B3:M3)

COUNTA returns number of cells with data in
Click to expand...

This will still average all cells, even the blank ones.? Confused?
 
=COUNT(B3:M3) will count cells with numbers.

=SUM(B3:M3) will sum those numbers.

=SUM(B3:M3)/COUNT(B3:M3) is what you need.


Gord
 
=COUNT(B3:M3) will count cells with numbers.
=SUM(B3:M3)  will sum those numbers.
=SUM(B3:M3)/COUNT(B3:M3)  is what you need.
Click to expand...

When is that different from AVERAGE(B3:M3)?

As you pointed out correctly earlier, AVERAGE automagically ignores
blank cells and cells with text, which includes the null string.
 
I was just pointing out a way to prove the "ignore blanks" theory.

Bob would see that either method..........AVERAGE.......or SUM/COUNT gives same
results.


Gord
 
I was just pointing out a way to prove the "ignore blanks"
theory. Bob would see that either method...AVERAGE...or
SUM/COUNT gives same results.
Click to expand...

"You can lead a horse to water, but you cannot make him drink" ;-).

As I noted elsewhere in this thread, IMHO, Bob has not sufficiently
defined the problem. So any solution is a wild-a.s.s guess, which may
or may not point Bob in the right direction. Generally, I think we
are wasting Bob's time by throwing darts blindfolded.

If Bob wants help, he can answer question posed here. He can start by
providing a concrete example where AVERAGE does not provide the
correct answer. In that case, what is in B3:M3 and any other relevant
cells; what does AVERAGE return; and what does Bob want AVERAGE to
return instead? That will give us insight into Bob's requirements
that Bob's description fails to illuminate, IMHO.

And Bob can tell us what version of Excel he has so that we might
provide the best solution for him, as I indicated previously.
 
"You can lead a horse to water, but you cannot make him drink" ;-).

As I noted elsewhere in this thread, IMHO, Bob has not sufficiently
defined the problem.  So any solution is a wild-a.s.s guess, which may
or may not point Bob in the right direction.  Generally, I think we
are wasting Bob's time by throwing darts blindfolded.

If Bob wants help, he can answer question posed here.  He can start by
providing a concrete example where AVERAGE does not provide the
correct answer.  In that case, what is in B3:M3 and any other relevant
cells; what does AVERAGE return; and what does Bob want AVERAGE to
return instead?  That will give us insight into Bob's requirements
that Bob's description fails to illuminate, IMHO.

And Bob can tell us what version of Excel he has so that we might
provide the best solution for him, as I indicated previously.
Click to expand...

Wow! Thanks guys for your help!!! I'll try the examples. Again Thanks
so Much!
 
Back
Top