how do i make a formula with varied discount percentages
 

First let me say Hello as this is my first time here :)

I am trying to make a column that as a varied percent discount. By this
I mean I want to make it so it deducts 5.25% from items sold with a
value of £0.01 - £29.99 But if the item sold is higher than £29.99
then it needs to Deduct 5.25% for the first £29.99 then to deduct
3.25% for the rest. Hope this makes sense.
Thanks in advance Steve

how do i make a formula with varied discount percentages
 

Assuming that A1 contains the value of the item sold, try...

=SUMPRODUCT(--(A1{0,29.99}),A1-{0,29.99},{0.0525,-0.02})

Hope this helps!

In article . com,
"ouch" wrote:

First let me say Hello as this is my first time here :)

I am trying to make a column that as a varied percent discount. By this
I mean I want to make it so it deducts 5.25% from items sold with a
value of £0.01 - £29.99 But if the item sold is higher than £29.99
then it needs to Deduct 5.25% for the first £29.99 then to deduct
3.25% for the rest. Hope this makes sense.
Thanks in advance Steve

how do i make a formula with varied discount percentages
 

"Domenic" wrote in message
...
In article . com,
"ouch" wrote:

First let me say Hello as this is my first time here :)

I am trying to make a column that as a varied percent discount. By this
I mean I want to make it so it deducts 5.25% from items sold with a
value of £0.01 - £29.99 But if the item sold is higher than £29.99
then it needs to Deduct 5.25% for the first £29.99 then to deduct
3.25% for the rest. Hope this makes sense.
Thanks in advance Steve

Assuming that A1 contains the value of the item sold, try...

=SUMPRODUCT(--(A1{0,29.99}),A1-{0,29.99},{0.0525,-0.02})

or
=A1-5.25%*MIN(A1,29.99)-3.25%*MAX(0,A1-29.99)
or
=A1*(1-5.25%)+2%*MAX(0,A1-29.99)
--
David Biddulph

how do i make a formula with varied discount percentages
 

Thanks guys all sorted for now :)

David Biddulph wrote:

"Domenic" wrote in message
...
In article . com,
"ouch" wrote:

First let me say Hello as this is my first time here :)

I am trying to make a column that as a varied percent discount. By this
I mean I want to make it so it deducts 5.25% from items sold with a
value of £0.01 - £29.99 But if the item sold is higher than £29.99
then it needs to Deduct 5.25% for the first £29.99 then to deduct
3.25% for the rest. Hope this makes sense.
Thanks in advance Steve

Assuming that A1 contains the value of the item sold, try...

=SUMPRODUCT(--(A1{0,29.99}),A1-{0,29.99},{0.0525,-0.02})

or
=A1-5.25%*MIN(A1,29.99)-3.25%*MAX(0,A1-29.99)
or
=A1*(1-5.25%)+2%*MAX(0,A1-29.99)
--
David Biddulph