Average Query

[Gary]

Hi All....I posted the following query few days back and got a couple of
answers. But the problem is none of'em seem to work. All the formulas that
you guys suggested giving me a VALUE error.

The formulae suggested by few of you were....

=SUMPRODUCT(--(MONTH(B2:AF2)=1),--(B3:AF3))

=AVERAGE(IF(MONTH(B2:AF2)=1,B3:AF3)) (array formula)

=SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF2<>"")*B3:AF3)/
SUMPRODUCT((MONTH(B2:AF2)=1)*(B2:AF<>""))

please go through the following query once again and suggest some better
solutions.

Thanks a ton.


in row 2 I have dates jan 1 to jun 31
in row 3 I have some data.
now if in cell A60 i need the average of the data in row 3 only for
January....so if Jan starts in cell B2 and ends in cell AF2, I need the
average of data in B3 to AF3

 

[Guest]

Gary,
I respectfully ask you to look at your data. I tried all three
formulae and all gave the expected and CORRECT results.

Your value error is likely to be data.

 

[T. Valko]

If you want an AVERAGE forget about formula #1.

My preference would be to use formula #2, the array formula.

You noted that it was an array formula BUT did you enter it as an array? Did
you use the key combination of CTRL,SHIFT,ENTER (not just ENTER)?

Are there any EMPTY cells within your DATE range? An EMPTY cell will
evaluate as month 1 (although that won't cause a #VALUE! error)

Are your dates true Excel dates or are they just TEXT strings?

I'm guessing your problem is that the dates are not DATES but are actually
TEXT strings.

If B2 = Jan 1

What is the result of this formula:

=ISNUMBER(B2)

If the result is FALSE then your dates are TEXT strings and the error is
being generated by the call to MONTH.

Biff

 

[Gary]

Hi Toppers.

I have tried it...the formula AVERAGE(IF(MONTH($C$2:$Y$2)=1,$C$4:$Y$4))
works for January but not for any other month. if in the next column I write
AVERAGE(IF(MONTH($C$2:$Y$2)=2,$C$4:$Y$4)), it should give me the Average of
the data under Feb but it's not doing that.
And even in January, what this formula is doing is Sum of all the populated
cells in row 4 / number of days in january. that is not what i want..i want
running average...for example...sum of populated cells in row 4 / count of
populated cells....I dont it to take al the blank cells under January in to
the account.

Thanks
Gary

 

[Don Guillett]

Please give an example of your data layout or send me a small workbook
sample along with copies of ALL of this thread and a clear explanation of
what you want.

 

[Don Guillett]

Gary wasn't using the figures past Jan so couldn't get Feb.
y2 should have been eb2