Average

[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!

 

[Gord Dibben]

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

 

[Bob]

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 -

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

 

[joeu2004]

AVERAGE function ignores blank cells, text cells and cells
with formulas that return ""

..... Which is text ;-).

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

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

 

[Gord Dibben]

Sorry Bob

Was not thinking about it in that respect.


Gord

 

[Bob]

How about doing your own average. Something like..

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

COUNTA returns number of cells with data in

This will still average all cells, even the blank ones.? Confused?

 

[Gord Dibben]

=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

 

[joeu2004]

=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.

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.

 

[Gord Dibben]

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

 

[joeu2004]

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.

"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.

 

[Bob]

"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.

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