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

On Tue, 21 Jun 2011 17:48:42 -0700 (PDT), Bob <(E-Mail Removed)> wrote:

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

On Jun 21, 9:36*pm, Gord Dibben <phnor...@shaw.ca> wrote:
> 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
>
>
>
> On Tue, 21 Jun 2011 17:48:42 -0700 (PDT), Bob <bobandrich...@comcast.net>wrote:
> >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!- Hide quoted text -
>
> - 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!!!

On Jun 21, 6:36*pm, Gord Dibben <phnor...@shaw.ca> wrote:
> AVERAGE function ignores blank cells, text cells and cells
> with formulas that return ""

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

Gord wrote:
> 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)).

Sorry Bob

Was not thinking about it in that respect.


Gord

On Tue, 21 Jun 2011 19:27:42 -0700 (PDT), Bob <(E-Mail Removed)> wrote:

>On Jun 21, 9:36*pm, Gord Dibben <phnor...@shaw.ca> wrote:
>> 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
>>
>>
>>
>> On Tue, 21 Jun 2011 17:48:42 -0700 (PDT), Bob <bobandrich...@comcast.net> wrote:
>> >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!- Hide quoted text -
>>
>> - 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!!!

On Jun 22, 3:58*am, CWatters <colin.watt...@NOturnersoakSPAM.plus.net>
wrote:
> On 22/06/2011 01:48, Bob wrote:
>
> > 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!
>
> 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?

=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


On Wed, 22 Jun 2011 04:06:23 -0700 (PDT), Bob <(E-Mail Removed)> wrote:

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

On Jun 22, 6:29*am, Gord Dibben <phnor...@shaw.ca> wrote:
> =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.

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

On Wed, 22 Jun 2011 07:26:13 -0700 (PDT), joeu2004 <(E-Mail Removed)> wrote:

>On Jun 22, 6:29*am, Gord Dibben <phnor...@shaw.ca> wrote:
>> =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.

On Jun 22, 12:18*pm, Gord Dibben <phnor...@shaw.ca> wrote:
> 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.