Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Help
Hello
I need to modify my sumproduct formula: SUMPRODUCT(ROUNDUP(Q4:AY4*1.02,0)) Such that if Q4:AY4 0 ROUNDUP(Q4:AY4*1.02,0) and if < 0 abs(Q4:AY4) Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Help
You could try =SUMPRODUCT(ROUNDUP(Q4:AY4*(IF(Q4:AY4<0,-1,1.02)),0)) as an
array formula Or =SUMPRODUCT(ROUNDUP(Q8:AY8*(0.1+1.01*SIGN(Q8:AY8)) ,0)), which doesn't need to be an array formula. -- David Biddulph "Sandy" wrote in message ... Hello I need to modify my sumproduct formula: SUMPRODUCT(ROUNDUP(Q4:AY4*1.02,0)) Such that if Q4:AY4 0 ROUNDUP(Q4:AY4*1.02,0) and if < 0 abs(Q4:AY4) Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Help
Two problems in my second formula:
Of course the 8s in the cell references should have been 4s. I was testing with a copy on another row. More significantly I slipped a decimal point! =SUMPRODUCT(ROUNDUP(Q4:AY4*(0.01+1.01*SIGN(Q4:AY4) ),0)) -- David Biddulph "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... You could try =SUMPRODUCT(ROUNDUP(Q4:AY4*(IF(Q4:AY4<0,-1,1.02)),0)) as an array formula Or =SUMPRODUCT(ROUNDUP(Q8:AY8*(0.1+1.01*SIGN(Q8:AY8)) ,0)), which doesn't need to be an array formula. -- David Biddulph "Sandy" wrote in message ... Hello I need to modify my sumproduct formula: SUMPRODUCT(ROUNDUP(Q4:AY4*1.02,0)) Such that if Q4:AY4 0 ROUNDUP(Q4:AY4*1.02,0) and if < 0 abs(Q4:AY4) Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Help
if you are going to use an array formula, why bother with SP
=SUM(ROUNDUP(Q4:AY4*(IF(Q4:AY4<0,-1,1.02)),0)) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "David Biddulph" <groups [at] biddulph.org.uk wrote in message ... You could try =SUMPRODUCT(ROUNDUP(Q4:AY4*(IF(Q4:AY4<0,-1,1.02)),0)) as an array formula Or =SUMPRODUCT(ROUNDUP(Q8:AY8*(0.1+1.01*SIGN(Q8:AY8)) ,0)), which doesn't need to be an array formula. -- David Biddulph "Sandy" wrote in message ... Hello I need to modify my sumproduct formula: SUMPRODUCT(ROUNDUP(Q4:AY4*1.02,0)) Such that if Q4:AY4 0 ROUNDUP(Q4:AY4*1.02,0) and if < 0 abs(Q4:AY4) Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Help
Thanks David
In the non-array formula what is the *.1+1.01 bit ? "David Biddulph" wrote: You could try =SUMPRODUCT(ROUNDUP(Q4:AY4*(IF(Q4:AY4<0,-1,1.02)),0)) as an array formula Or =SUMPRODUCT(ROUNDUP(Q8:AY8*(0.1+1.01*SIGN(Q8:AY8)) ,0)), which doesn't need to be an array formula. -- David Biddulph "Sandy" wrote in message ... Hello I need to modify my sumproduct formula: SUMPRODUCT(ROUNDUP(Q4:AY4*1.02,0)) Such that if Q4:AY4 0 ROUNDUP(Q4:AY4*1.02,0) and if < 0 abs(Q4:AY4) Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumProduct Help
As I pointed out in another post, I screwed up my decimal point in there,
and the 0.1 should have been 0.01 (so I'm not surprised that you didn't understand the formula with the error in it). :-( You have to remember the precedence of the operators. It's not *.01+1.01, it's *(0.01+(1.01*SIGN(...))), so when SIGN() is +ve you multiply by +1.02, and when sign is -ve you multiply by -1.00. -- David Biddulph "Sandy" wrote in message ... Thanks David In the non-array formula what is the *.1+1.01 bit ? "David Biddulph" wrote: You could try =SUMPRODUCT(ROUNDUP(Q4:AY4*(IF(Q4:AY4<0,-1,1.02)),0)) as an array formula Or =SUMPRODUCT(ROUNDUP(Q8:AY8*(0.1+1.01*SIGN(Q8:AY8)) ,0)), which doesn't need to be an array formula. -- David Biddulph "Sandy" wrote in message ... Hello I need to modify my sumproduct formula: SUMPRODUCT(ROUNDUP(Q4:AY4*1.02,0)) Such that if Q4:AY4 0 ROUNDUP(Q4:AY4*1.02,0) and if < 0 abs(Q4:AY4) Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
SUMPRODUCT + CSE | Excel Worksheet Functions | |||
Sumproduct(N and -- | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct | Excel Worksheet Functions |