• 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.

Multiply cells in a range based on criteria

polarisking

Member
I should be able to do this, but I'm having difficulty. I have a range of values (A1:A5). Based on whether the value is Positive or Negative, I want to MULTIPLY the values together.

Example
1
2
-3
4
-5

The formula (certainly an array/CSE type, I would think) would arrive at 8 (1* 2 * 4) for the positive set, and 15 (-3 * -5) for the negative set.

Actually, this is a return compounding exercise, so each value gets divided by 100 and then added to 1.

So the range becomes
1.01
1.02
.97
1.04
.95

I'd like to do this not using helper columns. I tried something like

SUMPRODUCT((1+A1:A5/100)*--(A1:A5>=0)) for the positive value and
SUMPRODUCT((1+A1:A5/100)*--(A1:A5<0)) for the negative value.

The result is the sum of the eligible values. I'm looking for the PRODUCT of the eligible values.

The positive result should be 1.071408 and negative result should be 0.9215.

Thanks in advance.
 

Attachments

  • Array CSE Problem.xlsx
    10.2 KB · Views: 4
hello mr.
Chihiro
very good answer
i have elso another without array , for NEGATIVE Result
Code:
=SUMPRODUCT(--($A$1:$A$5<0),$A$1:$A$5)/100+1
for POSITIVE Result
Code:
=SUMPRODUCT(--($A$1:$A$5>-1),$A$1:$A$5)/100+1
 

Attachments

  • Array CSE Problem1.xlsx
    10.7 KB · Views: 6
Or.............

For negative value sum:
=SUMPRODUCT(0+TEXT(A1:A5,"\0;-0"))/100+1

For positive value sum:
=SUMPRODUCT(0+TEXT(A1:A5,"0;\0"))/100+1

Regards
Bosco
 
Hi ,

The answers given by Chihiro and the answers given by the others are doing two different calculations , resulting in different answers.

Which calculation is the right one depends on what the OP is looking for.

But going by the second example of the values

1.01
1.02
.97
1.04
.95

I think Chihiro's formula works correctly to return these values , whereas the other two do not.

Narayan
 
Hi ,

The answers given by Chihiro and the answers given by the others are doing two different calculations , resulting in different answers.

Which calculation is the right one depends on what the OP is looking for.

But going by the second example of the values

1.01
1.02
.97
1.04
.95

I think Chihiro's formula works correctly to return these values , whereas the other two do not.

Narayan
Hi, Narayan

You are correct, and thanks for your advice.

SUMPRODUCT() is plus range of data e.g. =A1+A2+A3, while PRODUCT() is multiply range of data e,g, =A1*A2*A3

So, please ignore my formulas post in level #.04 and the non-array formulas will be fixed by this :

1] Product of negative value result:
=PRODUCT(INDEX(TEXT(A1:A5,"\0;-0.00")/100+1,0))

2] Product of positive value result:
=PRODUCT(INDEX(TEXT(A1:A5,"0.00;\0")/100+1,0))

Regards
Bosco
 
Last edited:
Back
Top