|
sumproduct question
I am having an issue with getting the sumproduct to return the
information I need. I am looking down a column and summing all the data that does not state "inactive" it can range from 0% to 100% =SUMPRODUCT((B39:B416=B18)*(N39:N416=0)*(N39:N416 <"Inactive")) This part is correct, cause I am looking for 3 instances which it gives me. But where I am having the problem is this: of those 3 instances; I have a 0%, 100% and a 100% which averages out at 66.67%. How do I take the above formula and add in a part to average the actual and only the numeric data? Thanks! Hans |
sumproduct question
=AVERAGE(IF((B39:B416=B18)*(N39:N416=0)*(N39:N416 <"Inactive"),A39:A416))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I am having an issue with getting the sumproduct to return the information I need. I am looking down a column and summing all the data that does not state "inactive" it can range from 0% to 100% =SUMPRODUCT((B39:B416=B18)*(N39:N416=0)*(N39:N416 <"Inactive")) This part is correct, cause I am looking for 3 instances which it gives me. But where I am having the problem is this: of those 3 instances; I have a 0%, 100% and a 100% which averages out at 66.67%. How do I take the above formula and add in a part to average the actual and only the numeric data? Thanks! Hans |
sumproduct question
Bob,
I circled around this, but never quite came up with your solution. Never thought about average AND if! Hey it works like a charm!!! As usual!! Thanks Again for all of your help, Hans Bob Phillips wrote: =AVERAGE(IF((B39:B416=B18)*(N39:N416=0)*(N39:N416 <"Inactive"),A39:A416)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I am having an issue with getting the sumproduct to return the information I need. I am looking down a column and summing all the data that does not state "inactive" it can range from 0% to 100% =SUMPRODUCT((B39:B416=B18)*(N39:N416=0)*(N39:N416 <"Inactive")) This part is correct, cause I am looking for 3 instances which it gives me. But where I am having the problem is this: of those 3 instances; I have a 0%, 100% and a 100% which averages out at 66.67%. How do I take the above formula and add in a part to average the actual and only the numeric data? Thanks! Hans |
sumproduct question
Or maybe
=SUMPRODUCT((B39:B416=B18)*(N39:N416=0)*(N39:N416 <"Inactive")*(N39:N416)) /SUMPRODUCT((B39:B416=B18)*(N39:N416=0)*(N39:N416< "Inactive")) The numerator sums while the denominator counts. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bob Phillips" wrote in message ... =AVERAGE(IF((B39:B416=B18)*(N39:N416=0)*(N39:N416 <"Inactive"),A39:A416)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I am having an issue with getting the sumproduct to return the information I need. I am looking down a column and summing all the data that does not state "inactive" it can range from 0% to 100% =SUMPRODUCT((B39:B416=B18)*(N39:N416=0)*(N39:N416 <"Inactive")) This part is correct, cause I am looking for 3 instances which it gives me. But where I am having the problem is this: of those 3 instances; I have a 0%, 100% and a 100% which averages out at 66.67%. How do I take the above formula and add in a part to average the actual and only the numeric data? Thanks! Hans |
sumproduct question
Bernard,
Glad you posted this; I tried that very thing and never did get it to work...Do you have any ideas why it didn't? I tried entering normally and as an array Hans Bernard Liengme wrote: Or maybe =SUMPRODUCT((B39:B416=B18)*(N39:N416=0)*(N39:N416 <"Inactive")*(N39:N416)) /SUMPRODUCT((B39:B416=B18)*(N39:N416=0)*(N39:N416< "Inactive")) The numerator sums while the denominator counts. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bob Phillips" wrote in message ... =AVERAGE(IF((B39:B416=B18)*(N39:N416=0)*(N39:N416 <"Inactive"),A39:A416)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I am having an issue with getting the sumproduct to return the information I need. I am looking down a column and summing all the data that does not state "inactive" it can range from 0% to 100% =SUMPRODUCT((B39:B416=B18)*(N39:N416=0)*(N39:N416 <"Inactive")) This part is correct, cause I am looking for 3 instances which it gives me. But where I am having the problem is this: of those 3 instances; I have a 0%, 100% and a 100% which averages out at 66.67%. How do I take the above formula and add in a part to average the actual and only the numeric data? Thanks! Hans |
sumproduct question
You need to check that you have real numbers
What did you get - wrong value or an error message? Send me the file (to my private email not the newsgroup) -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email wrote in message oups.com... Bernard, Glad you posted this; I tried that very thing and never did get it to work...Do you have any ideas why it didn't? I tried entering normally and as an array Hans Bernard Liengme wrote: Or maybe =SUMPRODUCT((B39:B416=B18)*(N39:N416=0)*(N39:N416 <"Inactive")*(N39:N416)) /SUMPRODUCT((B39:B416=B18)*(N39:N416=0)*(N39:N416< "Inactive")) The numerator sums while the denominator counts. best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Bob Phillips" wrote in message ... =AVERAGE(IF((B39:B416=B18)*(N39:N416=0)*(N39:N416 <"Inactive"),A39:A416)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) wrote in message oups.com... I am having an issue with getting the sumproduct to return the information I need. I am looking down a column and summing all the data that does not state "inactive" it can range from 0% to 100% =SUMPRODUCT((B39:B416=B18)*(N39:N416=0)*(N39:N416 <"Inactive")) This part is correct, cause I am looking for 3 instances which it gives me. But where I am having the problem is this: of those 3 instances; I have a 0%, 100% and a 100% which averages out at 66.67%. How do I take the above formula and add in a part to average the actual and only the numeric data? Thanks! Hans |
| All times are GMT +1. The time now is 11:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com