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

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

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

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

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

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