help with complicated formula

[Chris]

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

=SUMPRODUCT((A12:I350="Pleasanton")*(AVERAGE(I12:I350)+(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?

 

[Fred Smith]

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,"Pleasanton",j12:j350)/countif(a12:a350,"Pleasanton")

Regards,
Fred.

 

[Chris]

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

=SUMPRODUCT((A12:I350="Pleasanton")*(AVERAGE(I12:I350)+(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.

 

[Chris]

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,"Pleasanton",j12:j350)/countif(a12:a350,"Pleasanton")

Regards,
Fred.







- Show quoted text -

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

 

[Fred Smith]

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

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,"Pleasanton",j12:j350)/countif(a12:a350,"Pleasanton")

Regards,
Fred.







- Show quoted text -

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

 

[Chris]

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







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.

 

[Ashish Mathur]

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

 

[Dave Peterson]

#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????

 

[Chris]

Hi,

Try this.

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

--
Regards,

Ashish Mathur
Microsoft Excel MVPwww.ashishmathur.com







- Show quoted text -

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

 

[Ashish Mathur]

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]

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







- Show quoted text -

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