Average Query
 

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

Average Query
 

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.

"Gary" wrote:

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

Average Query
 

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" wrote in message
...
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

Average Query
 

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



"Toppers" wrote in message
...
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.

"Gary" wrote:

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

Average Query
 

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
SalesAid Software

"Gary" wrote in message
...
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



"Toppers" wrote in message
...
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.

"Gary" wrote:

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

Average Query
 

Gary wasn't using the figures past Jan so couldn't get Feb.
y2 should have been eb2
--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...

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
SalesAid Software

"Gary" wrote in message
...
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



"Toppers" wrote in message
...
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.

"Gary" wrote:

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