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