|
Sumproduct Excluding Data
Is there a way to setup a formula using Sumproduct to exclude data?
Specifically, I'm trying to calculate a weighted average using the following formula... =SUMPRODUCT(--(CE!$G$2:$G$200="MMFund")*(CE!$F$2:$F$200<ISNUMBE R(SEARCH("gov",CE!$F$2:$F$200))),CE!$Z$2:$Z$200,CE !$P$2:$P$200)/SUMPRODUCT(--(CE!$G$2:$G$200="MMFund")*(CE!$F$2:$F$200<ISNUMBE R(SEARCH("gov",CE!$F$2:$F$200))),CE!$Z$2:$Z$200) The question is how do I re-write the term (CE!$F$2:$F$200<ISNUMBER(SEARCH("gov",CE!$F$2:$F$ 200))) so that the calculation does not include any data that has the word "gov" in column F Just for clarity....Column G represents Money Market Funds...Column Z is the investment amount....and Column P is the interest rate. Thank you |
Sumproduct Excluding Data
Hi,
You could try something like this =ISERR(SEARCH("gov",G5:G11)) This returns TRUE for all cells not containing gov. Adjust for your range. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Brian" wrote: Is there a way to setup a formula using Sumproduct to exclude data? Specifically, I'm trying to calculate a weighted average using the following formula... =SUMPRODUCT(--(CE!$G$2:$G$200="MMFund")*(CE!$F$2:$F$200<ISNUMBE R(SEARCH("gov",CE!$F$2:$F$200))),CE!$Z$2:$Z$200,CE !$P$2:$P$200)/SUMPRODUCT(--(CE!$G$2:$G$200="MMFund")*(CE!$F$2:$F$200<ISNUMBE R(SEARCH("gov",CE!$F$2:$F$200))),CE!$Z$2:$Z$200) The question is how do I re-write the term (CE!$F$2:$F$200<ISNUMBER(SEARCH("gov",CE!$F$2:$F$ 200))) so that the calculation does not include any data that has the word "gov" in column F Just for clarity....Column G represents Money Market Funds...Column Z is the investment amount....and Column P is the interest rate. Thank you |
Sumproduct Excluding Data
Perfect!!
Thanks Shane! "Shane Devenshire" wrote: Hi, You could try something like this =ISERR(SEARCH("gov",G5:G11)) This returns TRUE for all cells not containing gov. Adjust for your range. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Brian" wrote: Is there a way to setup a formula using Sumproduct to exclude data? Specifically, I'm trying to calculate a weighted average using the following formula... =SUMPRODUCT(--(CE!$G$2:$G$200="MMFund")*(CE!$F$2:$F$200<ISNUMBE R(SEARCH("gov",CE!$F$2:$F$200))),CE!$Z$2:$Z$200,CE !$P$2:$P$200)/SUMPRODUCT(--(CE!$G$2:$G$200="MMFund")*(CE!$F$2:$F$200<ISNUMBE R(SEARCH("gov",CE!$F$2:$F$200))),CE!$Z$2:$Z$200) The question is how do I re-write the term (CE!$F$2:$F$200<ISNUMBER(SEARCH("gov",CE!$F$2:$F$ 200))) so that the calculation does not include any data that has the word "gov" in column F Just for clarity....Column G represents Money Market Funds...Column Z is the investment amount....and Column P is the interest rate. Thank you |
| All times are GMT +1. The time now is 09:24 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com