Function HELP please???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Below is a function I've somewhat put together. It is taking the Value of
shift "A" from column A, then, it's getting the month/year value from column
"B" and finally I am wanting to average the numeric values that meet the
criteria within columns "A" and "B" and averaging the values in column "D"
per month (month being: May-04, June-04, July-04, etc, etc). I am getting the
wrong value returned compared to a manual calculation. Where it is failing?

=SUMPRODUCT(('A Shift'!A4:A107="A")+('A Shift'!B4:B107="May-04")+AVERAGE('A
Shift'!D5:D107))

Here is sample of the raw data prior to pulling it together:
A May-04 Airwear 5384
A May-04 Airwear 5280
A May-04 Ovation 5379
A Jun-04 Airwear 4488
A Jun-04 Ovation 4553
A Jun-04 Ovation 4960
A Jun-04 Airwear(C) 4980
A Jun-04 Airwear 4037
 
=SUMPRODUCT(--('A Shift'!A4:A107="A"),--(Text('A
Shift'!B4:B107,"mmm-yy")="May-04"),('A Shift'!D5:D107))/SUMPRODUCT(--('A
Shift'!A4:A107="A"),--(Text('A Shift'!B4:B107,"mmm-yy")="May-04"))



--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob:

Thanks for chiming in! However, I am getting a #Value! error when I place
the formula into the cell. Is there another reason for the #Value! error? In
the end, I want to get an average against the values in column "D". Am I
missing something, or am I not seeing it. I can tell that you are taking the
total of the entire "A" column and dividing it against the sum of column "D"
to get the average, is that it? Or, is it something else?
 
Jay,

The problem stems from your original formula. All ranges must be the same
size, so D5:D107 mus be D4:D107. I tested this and it works

=SUMPRODUCT(--('A Shift'!A4:A107="A"),--(TEXT('A
Shift'!B4:B107,"mmm-yy")="May-04"),('A Shift'!D4:D107))/SUMPRODUCT(--('A
Shift'!A4:A107="A"),--(TEXT('A Shift'!B4:B107,"mmm-yy")="May-04"))

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Bob:

Again, thanks for catching that error on my part. It now works well! You
guys are great!
 
Back
Top