• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

PRODUCT Function using Criteria

Hello,
Can PRODUCT be used with criteria for which values can be in the array? In the attached file, I have a PRODUCT formula
=PRODUCT(C3:C7)^(1/COUNT(C3:C7))
that calculates a geometric mean from LOS in the table below.
MDC LOS
1 5.0
2 6.2
2 4.9
4 3.8
2 5.1
What if I want to only use LOS values in the array for both PRODUCT and COUNT that were MDC=2, for example? I could probably use COUNTIF for the second argument, but how would I modify the PRODUCT function? This is only a sample; the actual LOS column is thousands of rows deep and will change every moth.

Thanks for your help.
Paul
 

Attachments

  • Product.xlsx
    10.1 KB · Views: 2
Paul

Yes

But maybe not initially as you expect

If you use:
=PRODUCT((C3:C7)*(B3:B7=2))^(1/COUNTIF(B3:B7,2)) Ctrl+Shift+Enter
the section in Red evaluates to: {0;6.2;4.9;0;5.1}
and hence the product will be 0 as anything times zero is zero

So to get around that you need to replace the 0's with 1's

Do that by simply adding another array with the opposite condition
=PRODUCT((C3:C7)*(B3:B7=2)+(B3:B7<>2))^(1/COUNTIF(B3:B7,2)) Ctrl+Shift+Enter

Now the internal product component (C3:C7)*(B3:B7=2)+(B3:B7<>2)
evaluates to: {1;6.2;4.9;1;5.1}

so the final formula is
=PRODUCT((C3:C7)*(B3:B7=2)+(B3:B7<>2))^(1/COUNTIF(B3:B7,2)) Ctrl+Shift+Enter
 
Hi ,

Excel has a GEOMEAN function in case you want to use it.

The formula would be an array formula , to be entered using CTRL SHIFT ENTER :

=GEOMEAN(IF(B3:B7 = 2, C3:C7))

Narayan
 
Paul

Yes

But maybe not initially as you expect

If you use:
=PRODUCT((C3:C7)*(B3:B7=2))^(1/COUNTIF(B3:B7,2)) Ctrl+Shift+Enter
the section in Red evaluates to: {0;6.2;4.9;0;5.1}
and hence the product will be 0 as anything times zero is zero

So to get around that you need to replace the 0's with 1's

Do that by simply adding another array with the opposite condition
=PRODUCT((C3:C7)*(B3:B7=2)+(B3:B7<>2))^(1/COUNTIF(B3:B7,2)) Ctrl+Shift+Enter

Now the internal product component (C3:C7)*(B3:B7=2)+(B3:B7<>2)
evaluates to: {1;6.2;4.9;1;5.1}

so the final formula is
=PRODUCT((C3:C7)*(B3:B7=2)+(B3:B7<>2))^(1/COUNTIF(B3:B7,2)) Ctrl+Shift+Enter
Paul

Yes

But maybe not initially as you expect

If you use:
=PRODUCT((C3:C7)*(B3:B7=2))^(1/COUNTIF(B3:B7,2)) Ctrl+Shift+Enter
the section in Red evaluates to: {0;6.2;4.9;0;5.1}
and hence the product will be 0 as anything times zero is zero

So to get around that you need to replace the 0's with 1's

Do that by simply adding another array with the opposite condition
=PRODUCT((C3:C7)*(B3:B7=2)+(B3:B7<>2))^(1/COUNTIF(B3:B7,2)) Ctrl+Shift+Enter

Now the internal product component (C3:C7)*(B3:B7=2)+(B3:B7<>2)
evaluates to: {1;6.2;4.9;1;5.1}

so the final formula is
=PRODUCT((C3:C7)*(B3:B7=2)+(B3:B7<>2))^(1/COUNTIF(B3:B7,2)) Ctrl+Shift+Enter
Paul

Yes

But maybe not initially as you expect

If you use:
=PRODUCT((C3:C7)*(B3:B7=2))^(1/COUNTIF(B3:B7,2)) Ctrl+Shift+Enter
the section in Red evaluates to: {0;6.2;4.9;0;5.1}
and hence the product will be 0 as anything times zero is zero

So to get around that you need to replace the 0's with 1's

Do that by simply adding another array with the opposite condition
=PRODUCT((C3:C7)*(B3:B7=2)+(B3:B7<>2))^(1/COUNTIF(B3:B7,2)) Ctrl+Shift+Enter

Now the internal product component (C3:C7)*(B3:B7=2)+(B3:B7<>2)
evaluates to: {1;6.2;4.9;1;5.1}

so the final formula is
=PRODUCT((C3:C7)*(B3:B7=2)+(B3:B7<>2))^(1/COUNTIF(B3:B7,2)) Ctrl+Shift+Enter

Thanks! That works. I have a question about geometric means: If I had 100 cases in each instance of MDC 2, and 1 case for the other MDCs, it seems that there isn't any way to weight for that. The formula structure is that the count must conform to what's being multiplied in the PRODUCT, i.e., if 5 items are being multiplied together, then the count used in the second expression must match. Is that correct, or is there another way?
 
Hi ,

Excel has a GEOMEAN function in case you want to use it.

The formula would be an array formula , to be entered using CTRL SHIFT ENTER :

=GEOMEAN(IF(B3:B7 = 2, C3:C7))

Narayan

I didn't know there was a GEOMEAN function. This formula is simpler than Hui's, so that's what I'd use. I do have a question about geometric means (similar to above, even with the GEOMEAN function): If I had 100 cases in each instance of MDC 2, and 1 case for the other MDCs, it seems that there isn't any way to weight for that. The formula structure is that the count must conform to what's being multiplied in the PRODUCT, i.e., if 5 items are being multiplied together, then the count used in the second expression must match (with GEOMEAN, the count is implicit, no separate expression needed, but the logic is the same). Is that correct, or is there another way?
 
Thats correct

Ultimately you are doing a weighted average and so the denominator must be the count of the No of records matching the criteria applied to the numerator

As always, use a small test set of data and check it manually
Then you learn what is happening and how it works
 
Hi ,

Excel has a GEOMEAN function in case you want to use it.

The formula would be an array formula , to be entered using CTRL SHIFT ENTER :

=GEOMEAN(IF(B3:B7 = 2, C3:C7))

Narayan

If I want to add a second criteria, is that possible? In the attached file, I added a second condition using AND, but something is wrong with the result. Can you fix it?
Paul
 

Attachments

  • Product.xlsx
    11 KB · Views: 2
try: =GEOMEAN(IF(C3:C7=2,IF(B3:B7=2017,D3:D7))) Ctrl+Shift+Enter

If you select the AND(C3:C7=2,B3:B7=2017) component of your original formula and evaluate it using F9 it returns a single FALSE

If you select the IF(C3:C7=2,IF(B3:B7=2017,D3:D7)) component of the recomended formula above it evaluates to: {FALSE;FALSE;4.9;FALSE;5.1}
 
Back
Top