help with complicated formula
 

I have this formula but it is not giving me the results I am
expecting:

=SUMPRODUCT((A12:I350="Pleasanton")*(AVERAGE(I12:I 350)+(AVERAGE
(J12:J350))))/2

I am trying to average the sum of 2 columns I & J where "Pleasanton"
is in the row. I cannot use the AverageIF formula due to some
limitations on another program I will be exporting the spreadsheet
into. Due to the large arrays I am working with I am not sure what
other method I can use if I can't use averageif.

Are there any workarounds?

help with complicated formula
 

What results are you getting, and what results do you expect?

One thing that looks odd is the range for "Pleasanton". Should it be
A12:A350?

One way to replicate Averageif is Sumif/Countif, as in:
=sumif(a12:a350,"Pleasanton",i12:i350)/countif(a12:a350,"Pleasanton")+sumif(a12:a350,"Ple asanton",j12:j350)/countif(a12:a350,"Pleasanton")

Regards,
Fred.

"Chris" wrote in message
...
I have this formula but it is not giving me the results I am
expecting:

=SUMPRODUCT((A12:I350="Pleasanton")*(AVERAGE(I12:I 350)+(AVERAGE
(J12:J350))))/2

I am trying to average the sum of 2 columns I & J where "Pleasanton"
is in the row. I cannot use the AverageIF formula due to some
limitations on another program I will be exporting the spreadsheet
into. Due to the large arrays I am working with I am not sure what
other method I can use if I can't use averageif.

Are there any workarounds?

help with complicated formula
 

On Mar 11, 3:12*pm, Chris wrote:
I have this formula but it is not giving me the results I am
expecting:

=SUMPRODUCT((A12:I350="Pleasanton")*(AVERAGE(I12:I 350)+(AVERAGE
(J12:J350))))/2

I am trying to average the sum of 2 columns I & J where "Pleasanton"
is in the row. I cannot use the AverageIF formula due to some
limitations on another program I will be exporting the spreadsheet
into. Due to the large arrays I am working with I am not sure what
other method I can use if I can't use averageif.

Are there any workarounds?

Also, my program does not support array (ctr,shift,enter) formulas.

help with complicated formula
 

On Mar 11, 3:23*pm, "Fred Smith" wrote:
What results are you getting, and what results do you expect?

One thing that looks odd is the range for "Pleasanton". Should it be
A12:A350?

One way to replicate Averageif is Sumif/Countif, as in:
=sumif(a12:a350,"Pleasanton",i12:i350)/countif(a12:a350,"Pleasanton")+sumif*(a12:a350,"Pl easanton",j12:j350)/countif(a12:a350,"Pleasanton")

Regards,
Fred.

"Chris" wrote in message

...



I have this formula but it is not giving me the results I am
expecting:

=SUMPRODUCT((A12:I350="Pleasanton")*(AVERAGE(I12:I 350)+(AVERAGE
(J12:J350))))/2

I am trying to average the sum of 2 columns I & J where "Pleasanton"
is in the row. I cannot use the AverageIF formula due to some
limitations on another program I will be exporting the spreadsheet
into. Due to the large arrays I am working with I am not sure what
other method I can use if I can't use averageif.

Are there any workarounds?- Hide quoted text -

- Show quoted text -

Getting a #name? error. I wanted the entire range to be searched for
"pleasanton" hence the a12:I350, "Pleasanton"

help with complicated formula
 

1. The reason you are getting a #Name error is you used AVERAGE inside of
Sumproduct. Excel thinks this is a named range, and therefore reports that
it is missing. You can't get Sumproduct to average this way. However, as
demonstrated, you can use Sumif/Countif.

2. What version of Excel are you using that doesn't support
Ctrl-Shift-Enter?

3. Your use of the range a12:i350 is most unusual. If you're trying to
average column I, why would it have "Pleasonton" in it?

4. If you still want to check the entire range, what do you want to happen
when the entries don't correspond? Suppose A12 has "Pleasonton", B12 has it
as well, but C12 doesn't. Do you want this counted, or not?

5. What happened when you tried my suggested formula?

6. Please top post your replies.

Regards,
Fred


"Chris" wrote in message
...
On Mar 11, 3:23 pm, "Fred Smith" wrote:
What results are you getting, and what results do you expect?

One thing that looks odd is the range for "Pleasanton". Should it be
A12:A350?

One way to replicate Averageif is Sumif/Countif, as in:
=sumif(a12:a350,"Pleasanton",i12:i350)/countif(a12:a350,"Pleasanton")+sumif*(a12:a350,"Pl easanton",j12:j350)/countif(a12:a350,"Pleasanton")

Regards,
Fred.

"Chris" wrote in message

...



I have this formula but it is not giving me the results I am
expecting:

=SUMPRODUCT((A12:I350="Pleasanton")*(AVERAGE(I12:I 350)+(AVERAGE
(J12:J350))))/2

I am trying to average the sum of 2 columns I & J where "Pleasanton"
is in the row. I cannot use the AverageIF formula due to some
limitations on another program I will be exporting the spreadsheet
into. Due to the large arrays I am working with I am not sure what
other method I can use if I can't use averageif.

Are there any workarounds?- Hide quoted text -

- Show quoted text -

Getting a #name? error. I wanted the entire range to be searched for
"pleasanton" hence the a12:I350, "Pleasanton"

help with complicated formula
 

On Mar 11, 5:17*pm, "Fred Smith" wrote:
1. The reason you are getting a #Name error is you used AVERAGE inside of
Sumproduct. Excel thinks this is a named range, and therefore reports that
it is missing. You can't get Sumproduct to average this way. However, as
demonstrated, you can use Sumif/Countif.

2. What version of Excel are you using that doesn't support
Ctrl-Shift-Enter?

3. Your use of the range a12:i350 is most unusual. If you're trying to
average column I, why would it have "Pleasonton" in it?

4. If you still want to check the entire range, what do you want to happen
when the entries don't correspond? Suppose A12 has "Pleasonton", B12 has it
as well, but C12 doesn't. Do you want this counted, or not?

5. What happened when you tried my suggested formula?

6. Please top post your replies.

Regards,
Fred

"Chris" wrote in message

...
On Mar 11, 3:23 pm, "Fred Smith" wrote:





What results are you getting, and what results do you expect?

One thing that looks odd is the range for "Pleasanton". Should it be
A12:A350?

One way to replicate Averageif is Sumif/Countif, as in:
=sumif(a12:a350,"Pleasanton",i12:i350)/countif(a12:a350,"Pleasanton")+sumif**(a12:a350,"P leasanton",j12:j350)/countif(a12:a350,"Pleasanton")

Regards,
Fred.

"Chris" wrote in message

....

I have this formula but it is not giving me the results I am
expecting:

=SUMPRODUCT((A12:I350="Pleasanton")*(AVERAGE(I12:I 350)+(AVERAGE
(J12:J350))))/2

I am trying to average the sum of 2 columns I & J where "Pleasanton"
is in the row. I cannot use the AverageIF formula due to some
limitations on another program I will be exporting the spreadsheet
into. Due to the large arrays I am working with I am not sure what
other method I can use if I can't use averageif.

Are there any workarounds?- Hide quoted text -

- Show quoted text -

Getting a #name? error. I wanted the entire range to be searched for
"pleasanton" hence the a12:I350, "Pleasanton"- Hide quoted text -

- Show quoted text -

Sorry, maybe I was not clear. It's not the version of excel I am using
that is the issue, it's the program the dashboard application that
reads excel that does not support formulas that are entered as arrays.
The a12:i350 (my error should be a12:h:350 and not include the column
with the numbers to be averaged).

Your formula worked fine - for some reason when I initially copied it
over it added an extra character that gave me the name error.

The only thing I need help with is expanding the range to look for
"Pleasanton" from a12:A:350 to a12:H350. If "Pleasanton" isn't in any
of the other areas, then it should not count the corresponding numbers
in I or J to average.

help with complicated formula
 

Hi,

Try this.

=SUMPRODUCT((A12:I350=B16)*I12:J350)/COUNTIF(A12:I350,B16), where B16 holds
Pleasanton.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Chris" wrote in message
...
I have this formula but it is not giving me the results I am
expecting:

=SUMPRODUCT((A12:I350="Pleasanton")*(AVERAGE(I12:I 350)+(AVERAGE
(J12:J350))))/2

I am trying to average the sum of 2 columns I & J where "Pleasanton"
is in the row. I cannot use the AverageIF formula due to some
limitations on another program I will be exporting the spreadsheet
into. Due to the large arrays I am working with I am not sure what
other method I can use if I can't use averageif.

Are there any workarounds?

help with complicated formula
 

#1. Isn't true.

I'm not it solves the problem for the OP, but you can use =average() in
=sumproduct().

I'd guess that there was a different problem that cased the #NAME? error--maybe
a cell in that contained that same error????



Fred Smith wrote:

1. The reason you are getting a #Name error is you used AVERAGE inside of
Sumproduct. Excel thinks this is a named range, and therefore reports that
it is missing. You can't get Sumproduct to average this way. However, as
demonstrated, you can use Sumif/Countif.

2. What version of Excel are you using that doesn't support
Ctrl-Shift-Enter?

3. Your use of the range a12:i350 is most unusual. If you're trying to
average column I, why would it have "Pleasonton" in it?

4. If you still want to check the entire range, what do you want to happen
when the entries don't correspond? Suppose A12 has "Pleasonton", B12 has it
as well, but C12 doesn't. Do you want this counted, or not?

5. What happened when you tried my suggested formula?

6. Please top post your replies.

Regards,
Fred

"Chris" wrote in message
...
On Mar 11, 3:23 pm, "Fred Smith" wrote:
What results are you getting, and what results do you expect?

One thing that looks odd is the range for "Pleasanton". Should it be
A12:A350?

One way to replicate Averageif is Sumif/Countif, as in:
=sumif(a12:a350,"Pleasanton",i12:i350)/countif(a12:a350,"Pleasanton")+sumif*(a12:a350,"Pl easanton",j12:j350)/countif(a12:a350,"Pleasanton")

Regards,
Fred.

"Chris" wrote in message

...



I have this formula but it is not giving me the results I am
expecting:

=SUMPRODUCT((A12:I350="Pleasanton")*(AVERAGE(I12:I 350)+(AVERAGE
(J12:J350))))/2

I am trying to average the sum of 2 columns I & J where "Pleasanton"
is in the row. I cannot use the AverageIF formula due to some
limitations on another program I will be exporting the spreadsheet
into. Due to the large arrays I am working with I am not sure what
other method I can use if I can't use averageif.

Are there any workarounds?- Hide quoted text -

- Show quoted text -

Getting a #name? error. I wanted the entire range to be searched for
"pleasanton" hence the a12:I350, "Pleasanton"

--

Dave Peterson

help with complicated formula
 

On Mar 11, 7:03*pm, "Ashish Mathur" wrote:
Hi,

Try this.

=SUMPRODUCT((A12:I350=B16)*I12:J350)/COUNTIF(A12:I350,B16), where B16 holds
Pleasanton.

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

"Chris" wrote in message

...



I have this formula but it is not giving me the results I am
expecting:

=SUMPRODUCT((A12:I350="Pleasanton")*(AVERAGE(I12:I 350)+(AVERAGE
(J12:J350))))/2

I am trying to average the sum of 2 columns I & J where "Pleasanton"
is in the row. I cannot use the AverageIF formula due to some
limitations on another program I will be exporting the spreadsheet
into. Due to the large arrays I am working with I am not sure what
other method I can use if I can't use averageif.

Are there any workarounds?- Hide quoted text -

- Show quoted text -

Hi Ashish - I inputted your formula exactly and am getting an NA error.

help with complicated formula
 

Hi,

Well I guess it is not working because the range is A12:I350 in the
numerator. Try with the range A12:A350.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Chris" wrote in message
...
On Mar 11, 7:03 pm, "Ashish Mathur" wrote:
Hi,

Try this.

=SUMPRODUCT((A12:I350=B16)*I12:J350)/COUNTIF(A12:I350,B16), where B16
holds
Pleasanton.

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

"Chris" wrote in message

...



I have this formula but it is not giving me the results I am
expecting:

=SUMPRODUCT((A12:I350="Pleasanton")*(AVERAGE(I12:I 350)+(AVERAGE
(J12:J350))))/2

I am trying to average the sum of 2 columns I & J where "Pleasanton"
is in the row. I cannot use the AverageIF formula due to some
limitations on another program I will be exporting the spreadsheet
into. Due to the large arrays I am working with I am not sure what
other method I can use if I can't use averageif.

Are there any workarounds?- Hide quoted text -

- Show quoted text -

Hi Ashish - I inputted your formula exactly and am getting an NA error.

help with complicated formula
 

On Mar 12, 5:48*pm, "Ashish Mathur" wrote:
Hi,

Well I guess it is not working because the range is A12:I350 in the
numerator. *Try with the range A12:A350.

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

"Chris" wrote in message

...



On Mar 11, 7:03 pm, "Ashish Mathur" wrote:
Hi,

Try this.

=SUMPRODUCT((A12:I350=B16)*I12:J350)/COUNTIF(A12:I350,B16), where B16
holds
Pleasanton.

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com

"Chris" wrote in message

....

I have this formula but it is not giving me the results I am
expecting:

=SUMPRODUCT((A12:I350="Pleasanton")*(AVERAGE(I12:I 350)+(AVERAGE
(J12:J350))))/2

I am trying to average the sum of 2 columns I & J where "Pleasanton"
is in the row. I cannot use the AverageIF formula due to some
limitations on another program I will be exporting the spreadsheet
into. Due to the large arrays I am working with I am not sure what
other method I can use if I can't use averageif.

Are there any workarounds?- Hide quoted text -

- Show quoted text -

Hi Ashish - I inputted your formula exactly and am getting an NA error.- Hide quoted text -

- Show quoted text -

but I want to pick up the full range of a12:i350