Conditional SUMPRODUCT
 

Following seems simple enough, but I haven't figured it out so far.
I have some columns with percentages and dollar figures, like so:

T U
Range as Expected
% of Low Proceeds
2.28% $(4,797.10)
2.28% $(5,362.40)
2.28% $(5,122.40)
2.28% $(4,882.40)
2.28% $(5,222.70)
2.28% $(4,952.70)
2.28% $(5,203.00)
5.42% $(4,914.00)
5.42% $6,458.30
5.42% $6,648.00
3.12% $3,049.00
3.12% $5,353.20
3.12% $5,193.00
3.12% $5,278.20
3.12% $630.00
3.12% $14,010.00
3.12% $6,744.00
3.20% $6,672.00
3.20% $6,522.00
3.20% $6,372.00
3.20% $12,594.00
3.20% $6,222.00
3.20% $4,248.00
3.20% $5,437.80
3.20% $260.00


I want to run SUMPRODUCT but ignore the sign of the dollar
figure. That is, the answer should be as if the figures in
Column "U" are absolute-valued. I don't want to set up another
dummy column.

Thanks,
Dallman

Conditional SUMPRODUCT
 

Use the ABS( ) function in your SUMPRODUCT formula to return the
absolute (positive) value of column U.

Hope this helps.

Pete

Dallman Ross wrote:
Following seems simple enough, but I haven't figured it out so far.
I have some columns with percentages and dollar figures, like so:

T U
Range as Expected
% of Low Proceeds
2.28% $(4,797.10)
2.28% $(5,362.40)
2.28% $(5,122.40)
2.28% $(4,882.40)
2.28% $(5,222.70)
2.28% $(4,952.70)
2.28% $(5,203.00)
5.42% $(4,914.00)
5.42% $6,458.30
5.42% $6,648.00
3.12% $3,049.00
3.12% $5,353.20
3.12% $5,193.00
3.12% $5,278.20
3.12% $630.00
3.12% $14,010.00
3.12% $6,744.00
3.20% $6,672.00
3.20% $6,522.00
3.20% $6,372.00
3.20% $12,594.00
3.20% $6,222.00
3.20% $4,248.00
3.20% $5,437.80
3.20% $260.00


I want to run SUMPRODUCT but ignore the sign of the dollar
figure. That is, the answer should be as if the figures in
Column "U" are absolute-valued. I don't want to set up another
dummy column.

Thanks,
Dallman

Conditional SUMPRODUCT
 

In .com, Pete_UK
spake thusly [top-posting inverted]:

Dallman Ross wrote:
I want to run SUMPRODUCT but ignore the sign [. . .]

Use the ABS( ) function in your SUMPRODUCT formula to return the
absolute (positive) value of column U.

Okay, I couldn't get it to work previously, but now I did:

=SUMPRODUCT($T$2:$T$211,ABS($U$2:$U$211))/SUMPRODUCT(ABS($U$2:$U$211))

I had to use SUMPRODUCT instead of just SUM for the divisor; then
it worked.

---------------
I have some columns with percentages and dollar figures, like so:

T U
Range as Expected
% of Low Proceeds
2.28% $(4,797.10)
2.28% $(5,362.40)
2.28% $(5,122.40)
2.28% $(4,882.40)
2.28% $(5,222.70)
---------------

Thanks, Pete!