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 =A30.01 - =A329.99 But if the item sold is higher than =A329.99
then it needs to Deduct 5.25% for the first =A329.99 then to deduct
3=2E25% 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})

Hope this helps!

In article <[email protected]>,
"ouch" <[email protected]> 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

"Domenic" <[email protected]> wrote in message
news:[email protected]...
> In article <[email protected]>,
> "ouch" <[email protected]> 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

Thanks guys all sorted for now

David Biddulph wrote:

> "Domenic" <[email protected]> wrote in message
> news:[email protected]...
> > In article <[email protected]>,
> > "ouch" <[email protected]> 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 =A30.01 - =A329.99 But if the item sold is higher than =A329.=
99
> >> then it needs to Deduct 5.25% for the first =A329.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...
> >
> > =3DSUMPRODUCT(--(A1>{0,29.99}),A1-{0,29.99},{0.0525,-0.02})
>
> or
> =3DA1-5.25%*MIN(A1,29.99)-3.25%*MAX(0,A1-29.99)
> or
> =3DA1*(1-5.25%)+2%*MAX(0,A1-29.99)
> --=20
> David Biddulph