Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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" |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complicated formula | Excel Worksheet Functions | |||
I have a complicated formula that I need SERIOUS help with please! | Excel Discussion (Misc queries) | |||
Complicated Formula - I think | Excel Worksheet Functions | |||
Complicated formula | Excel Worksheet Functions | |||
Complicated Formula | Excel Discussion (Misc queries) |