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

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

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