ExcelBanter - Function HELP please???

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Function HELP please??? (https://www.excelbanter.com/excel-worksheet-functions/15410-function-help-please.html)

Function HELP please???

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)

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

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?

"Bob Phillips" wrote:

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

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

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)

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

"Bob Phillips" wrote:

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

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

Bob:

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

"Bob Phillips" wrote:

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)

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

"Bob Phillips" wrote:

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

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