Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CONDITIONAL RESULTS DEPENDING ON CELL'S NAME (sumproduct) | Excel Discussion (Misc queries) | |||
Sumproduct conditional | Excel Discussion (Misc queries) | |||
Sumproduct conditional with dates | Excel Discussion (Misc queries) | |||
Conditional sumproduct? | New Users to Excel | |||
Conditional Format Not Working | Excel Discussion (Misc queries) |