I want to calculate 3 different % from the same cell

I am making an invoice that will figure a discount for a certain amount of
sale, all from the same cell, if the subtotal is < 500 then they will get no
discount, if it is > 500 they will get a 5%, and if it is > 750 they will get
a 10% discount, what formula do i use??
--
cardfan057

You don't say what you want if the amount is exactly 500 or 750 but try:

=A1*(1-((A1>=750)+(A1>=500))*5%)

--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"cardfan057" <[email protected]> wrote in message
news:[email protected]...
>I am making an invoice that will figure a discount for a certain amount of
> sale, all from the same cell, if the subtotal is < 500 then they will get
> no
> discount, if it is > 500 they will get a 5%, and if it is > 750 they will
> get
> a 10% discount, what formula do i use??
> --
> cardfan057

=IF(AND(A1>=500,A1<750),A1*0.05,IF(A1>=750,A1*0.1,0))
Regards,
Alan.
"cardfan057" <[email protected]> wrote in message
news:[email protected]...
>I am making an invoice that will figure a discount for a certain amount of
> sale, all from the same cell, if the subtotal is < 500 then they will get
> no
> discount, if it is > 500 they will get a 5%, and if it is > 750 they will
> get
> a 10% discount, what formula do i use??
> --
> cardfan057

Sandy,
That is really good! Do I have this right? It took a while to realise how it
works,
1-((A1>=750)+(A1>=500))
converts the TRUE or FALSE results to 1 or 0 thus returning 1 or 2, then the
*5% returns 5% for 1 and 10% for 2 and A1 is multiplied by that?
Regards,
Alan.

"Sandy Mann" <[email protected]> wrote in message
news:[email protected]...
> You don't say what you want if the amount is exactly 500 or 750 but try:
>
> =A1*(1-((A1>=750)+(A1>=500))*5%)
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "cardfan057" <[email protected]> wrote in message
> news:[email protected]...
>>I am making an invoice that will figure a discount for a certain amount of
>> sale, all from the same cell, if the subtotal is < 500 then they will get
>> no
>> discount, if it is > 500 they will get a 5%, and if it is > 750 they will
>> get
>> a 10% discount, what formula do i use??
>> --
>> cardfan057
>
>

Thanks
--
cardfan057


"Alan" wrote:

> =IF(AND(A1>=500,A1<750),A1*0.05,IF(A1>=750,A1*0.1,0))
> Regards,
> Alan.
> "cardfan057" <[email protected]> wrote in message
> news:[email protected]...
> >I am making an invoice that will figure a discount for a certain amount of
> > sale, all from the same cell, if the subtotal is < 500 then they will get
> > no
> > discount, if it is > 500 they will get a 5%, and if it is > 750 they will
> > get
> > a 10% discount, what formula do i use??
> > --
> > cardfan057
>
>
>

Sandy's solution is far shorter, efficient and more elegant than mine,
Regards,
Alan.
"cardfan057" <[email protected]> wrote in message
news:[email protected]...
> Thanks
> --
> cardfan057
>
>
> "Alan" wrote:
>
>> =IF(AND(A1>=500,A1<750),A1*0.05,IF(A1>=750,A1*0.1,0))
>> Regards,
>> Alan.
>> "cardfan057" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am making an invoice that will figure a discount for a certain amount
>> >of
>> > sale, all from the same cell, if the subtotal is < 500 then they will
>> > get
>> > no
>> > discount, if it is > 500 they will get a 5%, and if it is > 750 they
>> > will
>> > get
>> > a 10% discount, what formula do i use??
>> > --
>> > cardfan057
>>
>>
>>

Alan,

Yes that's correct. Because Excel performs multiplication, (or division),
before subtraction, (or addition), it multiplies 5% by 1 or 2 depending what
is in A1 to give 5%, (0.05) or 10%, (0.1) which is then subtracted from 1 to
give 0.95 or 0.9 respectively which of course id 95% or 90%

--
Regards,

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"Alan" <[email protected]> wrote in message
news:%[email protected]...
> Sandy,
> That is really good! Do I have this right? It took a while to realise how
> it works,
> 1-((A1>=750)+(A1>=500))
> converts the TRUE or FALSE results to 1 or 0 thus returning 1 or 2, then
> the *5% returns 5% for 1 and 10% for 2 and A1 is multiplied by that?
> Regards,
> Alan.
>
> "Sandy Mann" <[email protected]> wrote in message
> news:[email protected]...
>> You don't say what you want if the amount is exactly 500 or 750 but try:
>>
>> =A1*(1-((A1>=750)+(A1>=500))*5%)
>>
>> --
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>>
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "cardfan057" <[email protected]> wrote in message
>> news:[email protected]...
>>>I am making an invoice that will figure a discount for a certain amount
>>>of
>>> sale, all from the same cell, if the subtotal is < 500 then they will
>>> get no
>>> discount, if it is > 500 they will get a 5%, and if it is > 750 they
>>> will get
>>> a 10% discount, what formula do i use??
>>> --
>>> cardfan057
>>
>>
>
>

This is what I am doing, I hope this helps.

Selling Quanity Amount
Price Purchased Purchased
$5.25 2 $10.50
55.00 2 110.00
105.99 4 423.96
38.70 2 77.40

(e15) 621.86
( D16) 5% (e16) 31.09

590.77
7% 41.35


$632.12

I am needing d16 to show 5% if e15 is >=500 and <750, and 10% if e15 is
>=750, I have e16 formulated = e15*d16
--
cardfan057


"Alan" wrote:

> Sandy's solution is far shorter, efficient and more elegant than mine,
> Regards,
> Alan.
> "cardfan057" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks
> > --
> > cardfan057
> >
> >
> > "Alan" wrote:
> >
> >> =IF(AND(A1>=500,A1<750),A1*0.05,IF(A1>=750,A1*0.1,0))
> >> Regards,
> >> Alan.
> >> "cardfan057" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I am making an invoice that will figure a discount for a certain amount
> >> >of
> >> > sale, all from the same cell, if the subtotal is < 500 then they will
> >> > get
> >> > no
> >> > discount, if it is > 500 they will get a 5%, and if it is > 750 they
> >> > will
> >> > get
> >> > a 10% discount, what formula do i use??
> >> > --
> >> > cardfan057
> >>
> >>
> >>
>
>
>

If you are referring to the formula that I posted and you want just the 5%
to show not, (as I posted), the result after the 5% is discounted then
simply remove the 1- and E15* from the formula to give:

=((E15>=750)+(E15>=500)*5%)

This will return 5% or 10% as appropriate

However you can do the would calculation in one cell with:

=E15*((E15>=750)+(E15>=500)*5%)

Which will return 31.093 from your example.


--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"cardfan057" <[email protected]> wrote in message
news:[email protected]...
> This is what I am doing, I hope this helps.
>
> Selling Quanity Amount
> Price Purchased Purchased
> $5.25 2 $10.50
> 55.00 2 110.00
> 105.99 4 423.96
> 38.70 2 77.40
>
> (e15) 621.86
> ( D16) 5% (e16) 31.09
>
> 590.77
> 7% 41.35
>
>
> $632.12
>
> I am needing d16 to show 5% if e15 is >=500 and <750, and 10% if e15 is
>>=750, I have e16 formulated = e15*d16
> --
> cardfan057
>
>
> "Alan" wrote:
>
>> Sandy's solution is far shorter, efficient and more elegant than mine,
>> Regards,
>> Alan.
>> "cardfan057" <[email protected]> wrote in message
>> news:[email protected]...
>> > Thanks
>> > --
>> > cardfan057
>> >
>> >
>> > "Alan" wrote:
>> >
>> >> =IF(AND(A1>=500,A1<750),A1*0.05,IF(A1>=750,A1*0.1,0))
>> >> Regards,
>> >> Alan.
>> >> "cardfan057" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >I am making an invoice that will figure a discount for a certain
>> >> >amount
>> >> >of
>> >> > sale, all from the same cell, if the subtotal is < 500 then they
>> >> > will
>> >> > get
>> >> > no
>> >> > discount, if it is > 500 they will get a 5%, and if it is > 750 they
>> >> > will
>> >> > get
>> >> > a 10% discount, what formula do i use??
>> >> > --
>> >> > cardfan057
>> >>
>> >>
>> >>
>>
>>
>>

Thanks for that Sandy,
We live and learn!
That's a way to do a calculation like that which never occured to me, I
always have disliked nested IF's!
Regards and thanks from Hertfordshire,
Alan.
"Sandy Mann" <[email protected]> wrote in message
news:[email protected]...
> Alan,
>
> Yes that's correct. Because Excel performs multiplication, (or division),
> before subtraction, (or addition), it multiplies 5% by 1 or 2 depending
> what
> is in A1 to give 5%, (0.05) or 10%, (0.1) which is then subtracted from 1
> to
> give 0.95 or 0.9 respectively which of course id 95% or 90%
>
> --
> Regards,
>
> Sandy
> In Perth, the ancient capital of Scotland
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "Alan" <[email protected]> wrote in message
> news:%[email protected]...
>> Sandy,
>> That is really good! Do I have this right? It took a while to realise how
>> it works,
>> 1-((A1>=750)+(A1>=500))
>> converts the TRUE or FALSE results to 1 or 0 thus returning 1 or 2, then
>> the *5% returns 5% for 1 and 10% for 2 and A1 is multiplied by that?
>> Regards,
>> Alan.
>>
>> "Sandy Mann" <[email protected]> wrote in message
>> news:[email protected]...
>>> You don't say what you want if the amount is exactly 500 or 750 but try:
>>>
>>> =A1*(1-((A1>=750)+(A1>=500))*5%)
>>>
>>> --
>>> HTH
>>>
>>> Sandy
>>> In Perth, the ancient capital of Scotland
>>>
>>> [email protected]
>>> [email protected] with @tiscali.co.uk
>>>
>>>
>>> "cardfan057" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>I am making an invoice that will figure a discount for a certain amount
>>>>of
>>>> sale, all from the same cell, if the subtotal is < 500 then they will
>>>> get no
>>>> discount, if it is > 500 they will get a 5%, and if it is > 750 they
>>>> will get
>>>> a 10% discount, what formula do i use??
>>>> --
>>>> cardfan057
>>>
>>>
>>
>>
>
>
>

When I apply =((E15>=750)+(E15>=500)*5%) and the total is over 750 it gives
me 105% instead of 10%.

--
cardfan057


"Sandy Mann" wrote:

> If you are referring to the formula that I posted and you want just the 5%
> to show not, (as I posted), the result after the 5% is discounted then
> simply remove the 1- and E15* from the formula to give:
>
> =((E15>=750)+(E15>=500)*5%)
>
> This will return 5% or 10% as appropriate
>
> However you can do the would calculation in one cell with:
>
> =E15*((E15>=750)+(E15>=500)*5%)
>
> Which will return 31.093 from your example.
>
>
> --
> HTH
>
> Sandy
> In Perth, the ancient capital of Scotland
>
> [email protected]
> [email protected] with @tiscali.co.uk
>
>
> "cardfan057" <[email protected]> wrote in message
> news:[email protected]...
> > This is what I am doing, I hope this helps.
> >
> > Selling Quanity Amount
> > Price Purchased Purchased
> > $5.25 2 $10.50
> > 55.00 2 110.00
> > 105.99 4 423.96
> > 38.70 2 77.40
> >
> > (e15) 621.86
> > ( D16) 5% (e16) 31.09
> >
> > 590.77
> > 7% 41.35
> >
> >
> > $632.12
> >
> > I am needing d16 to show 5% if e15 is >=500 and <750, and 10% if e15 is
> >>=750, I have e16 formulated = e15*d16
> > --
> > cardfan057
> >
> >
> > "Alan" wrote:
> >
> >> Sandy's solution is far shorter, efficient and more elegant than mine,
> >> Regards,
> >> Alan.
> >> "cardfan057" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Thanks
> >> > --
> >> > cardfan057
> >> >
> >> >
> >> > "Alan" wrote:
> >> >
> >> >> =IF(AND(A1>=500,A1<750),A1*0.05,IF(A1>=750,A1*0.1,0))
> >> >> Regards,
> >> >> Alan.
> >> >> "cardfan057" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> >I am making an invoice that will figure a discount for a certain
> >> >> >amount
> >> >> >of
> >> >> > sale, all from the same cell, if the subtotal is < 500 then they
> >> >> > will
> >> >> > get
> >> >> > no
> >> >> > discount, if it is > 500 they will get a 5%, and if it is > 750 they
> >> >> > will
> >> >> > get
> >> >> > a 10% discount, what formula do i use??
> >> >> > --
> >> >> > cardfan057
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>

Hello:

I don't mean to confuse the issue but I don't see how the current
recommendation will work. (May be I am missing something.)
Using the same general idea here is a formula
that you can put in D16 that will work and handle up 29
different discounts (which is the limit for choose).

=CHOOSE((E15>=750)+(E15>=500)+1,0,0.05,0.1)

To handle additional discounts simply add in another test.
If sales are above say $1000 you get 15% off then:

=CHOOSE((E15>=750)+(E15>=500)+(E15>=1000)+1,0,0.05,0.1,0.15)

Pieter Vandenberg


cardfan057 <[email protected]> wrote:
: When I apply =((E15>=750)+(E15>=500)*5%) and the total is over 750 it gives
: me 105% instead of 10%.

: --
: cardfan057


: "Sandy Mann" wrote:

:> If you are referring to the formula that I posted and you want just the 5%
:> to show not, (as I posted), the result after the 5% is discounted then
:> simply remove the 1- and E15* from the formula to give:
:>
:> =((E15>=750)+(E15>=500)*5%)
:>
:> This will return 5% or 10% as appropriate
:>
:> However you can do the would calculation in one cell with:
:>
:> =E15*((E15>=750)+(E15>=500)*5%)
:>
:> Which will return 31.093 from your example.
:>
:>
:> --
:> HTH
:>
:> Sandy
:> In Perth, the ancient capital of Scotland
:>
:> [email protected]
:> [email protected] with @tiscali.co.uk
:>
:>
:> "cardfan057" <[email protected]> wrote in message
:> news:[email protected]...
:> > This is what I am doing, I hope this helps.
:> >
:> > Selling Quanity Amount
:> > Price Purchased Purchased
:> > $5.25 2 $10.50
:> > 55.00 2 110.00
:> > 105.99 4 423.96
:> > 38.70 2 77.40
:> >
:> > (e15) 621.86
:> > ( D16) 5% (e16) 31.09
:> >
:> > 590.77
:> > 7% 41.35
:> >
:> >
:> > $632.12
:> >
:> > I am needing d16 to show 5% if e15 is >=500 and <750, and 10% if e15 is
:> >>=750, I have e16 formulated = e15*d16
:> > --
:> > cardfan057
:> >
:> >
:> > "Alan" wrote:
:> >
:> >> Sandy's solution is far shorter, efficient and more elegant than mine,
:> >> Regards,
:> >> Alan.
:> >> "cardfan057" <[email protected]> wrote in message
:> >> news:[email protected]...
:> >> > Thanks
:> >> > --
:> >> > cardfan057
:> >> >
:> >> >
:> >> > "Alan" wrote:
:> >> >
:> >> >> =IF(AND(A1>=500,A1<750),A1*0.05,IF(A1>=750,A1*0.1,0))
:> >> >> Regards,
:> >> >> Alan.
:> >> >> "cardfan057" <[email protected]> wrote in message
:> >> >> news:[email protected]...
:> >> >> >I am making an invoice that will figure a discount for a certain
:> >> >> >amount
:> >> >> >of
:> >> >> > sale, all from the same cell, if the subtotal is < 500 then they
:> >> >> > will
:> >> >> > get
:> >> >> > no
:> >> >> > discount, if it is > 500 they will get a 5%, and if it is > 750 they
:> >> >> > will
:> >> >> > get
:> >> >> > a 10% discount, what formula do i use??
:> >> >> > --
:> >> >> > cardfan057
:> >> >>
:> >> >>
:> >> >>
:> >>
:> >>
:> >>
:>
:>
:>

Thanks, that did the trick. I really appreciate it.
--
cardfan057


"vandenberg p" wrote:

> Hello:
>
> I don't mean to confuse the issue but I don't see how the current
> recommendation will work. (May be I am missing something.)
> Using the same general idea here is a formula
> that you can put in D16 that will work and handle up 29
> different discounts (which is the limit for choose).
>
> =CHOOSE((E15>=750)+(E15>=500)+1,0,0.05,0.1)
>
> To handle additional discounts simply add in another test.
> If sales are above say $1000 you get 15% off then:
>
> =CHOOSE((E15>=750)+(E15>=500)+(E15>=1000)+1,0,0.05,0.1,0.15)
>
> Pieter Vandenberg
>
>
> cardfan057 <[email protected]> wrote:
> : When I apply =((E15>=750)+(E15>=500)*5%) and the total is over 750 it gives
> : me 105% instead of 10%.
>
> : --
> : cardfan057
>
>
> : "Sandy Mann" wrote:
>
> :> If you are referring to the formula that I posted and you want just the 5%
> :> to show not, (as I posted), the result after the 5% is discounted then
> :> simply remove the 1- and E15* from the formula to give:
> :>
> :> =((E15>=750)+(E15>=500)*5%)
> :>
> :> This will return 5% or 10% as appropriate
> :>
> :> However you can do the would calculation in one cell with:
> :>
> :> =E15*((E15>=750)+(E15>=500)*5%)
> :>
> :> Which will return 31.093 from your example.
> :>
> :>
> :> --
> :> HTH
> :>
> :> Sandy
> :> In Perth, the ancient capital of Scotland
> :>
> :> [email protected]
> :> [email protected] with @tiscali.co.uk
> :>
> :>
> :> "cardfan057" <[email protected]> wrote in message
> :> news:[email protected]...
> :> > This is what I am doing, I hope this helps.
> :> >
> :> > Selling Quanity Amount
> :> > Price Purchased Purchased
> :> > $5.25 2 $10.50
> :> > 55.00 2 110.00
> :> > 105.99 4 423.96
> :> > 38.70 2 77.40
> :> >
> :> > (e15) 621.86
> :> > ( D16) 5% (e16) 31.09
> :> >
> :> > 590.77
> :> > 7% 41.35
> :> >
> :> >
> :> > $632.12
> :> >
> :> > I am needing d16 to show 5% if e15 is >=500 and <750, and 10% if e15 is
> :> >>=750, I have e16 formulated = e15*d16
> :> > --
> :> > cardfan057
> :> >
> :> >
> :> > "Alan" wrote:
> :> >
> :> >> Sandy's solution is far shorter, efficient and more elegant than mine,
> :> >> Regards,
> :> >> Alan.
> :> >> "cardfan057" <[email protected]> wrote in message
> :> >> news:[email protected]...
> :> >> > Thanks
> :> >> > --
> :> >> > cardfan057
> :> >> >
> :> >> >
> :> >> > "Alan" wrote:
> :> >> >
> :> >> >> =IF(AND(A1>=500,A1<750),A1*0.05,IF(A1>=750,A1*0.1,0))
> :> >> >> Regards,
> :> >> >> Alan.
> :> >> >> "cardfan057" <[email protected]> wrote in message
> :> >> >> news:[email protected]...
> :> >> >> >I am making an invoice that will figure a discount for a certain
> :> >> >> >amount
> :> >> >> >of
> :> >> >> > sale, all from the same cell, if the subtotal is < 500 then they
> :> >> >> > will
> :> >> >> > get
> :> >> >> > no
> :> >> >> > discount, if it is > 500 they will get a 5%, and if it is > 750 they
> :> >> >> > will
> :> >> >> > get
> :> >> >> > a 10% discount, what formula do i use??
> :> >> >> > --
> :> >> >> > cardfan057
> :> >> >>
> :> >> >>
> :> >> >>
> :> >>
> :> >>
> :> >>
> :>
> :>
> :>
>

The OP has a solution that works for him/her so Iam probably talking to
myself, but for the sake of the archives I goofed up when I cut up my
original formula. I tested the formula that calculated the whole thing in
one cell but omitted to test the % one.

It should have been:

=((E15>=750)+(E15>=500))*5%

The other formula however does work as advertised.
--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"cardfan057" <[email protected]> wrote in message
news:[email protected]...
> When I apply =((E15>=750)+(E15>=500)*5%) and the total is over 750 it
> gives
> me 105% instead of 10%.
>
> --
> cardfan057
>
>
> "Sandy Mann" wrote:
>
>> If you are referring to the formula that I posted and you want just the
>> 5%
>> to show not, (as I posted), the result after the 5% is discounted then
>> simply remove the 1- and E15* from the formula to give:
>>
>> =((E15>=750)+(E15>=500)*5%)
>>
>> This will return 5% or 10% as appropriate
>>
>> However you can do the would calculation in one cell with:
>>
>> =E15*((E15>=750)+(E15>=500)*5%)
>>
>> Which will return 31.093 from your example.
>>
>>
>> --
>> HTH
>>
>> Sandy
>> In Perth, the ancient capital of Scotland
>>
>> [email protected]
>> [email protected] with @tiscali.co.uk
>>
>>
>> "cardfan057" <[email protected]> wrote in message
>> news:[email protected]...
>> > This is what I am doing, I hope this helps.
>> >
>> > Selling Quanity Amount
>> > Price Purchased Purchased
>> > $5.25 2 $10.50
>> > 55.00 2 110.00
>> > 105.99 4 423.96
>> > 38.70 2 77.40
>> >
>> > (e15) 621.86
>> > ( D16) 5% (e16) 31.09
>> >
>> > 590.77
>> > 7% 41.35
>> >
>> >
>> > $632.12
>> >
>> > I am needing d16 to show 5% if e15 is >=500 and <750, and 10% if e15 is
>> >>=750, I have e16 formulated = e15*d16
>> > --
>> > cardfan057
>> >
>> >
>> > "Alan" wrote:
>> >
>> >> Sandy's solution is far shorter, efficient and more elegant than mine,
>> >> Regards,
>> >> Alan.
>> >> "cardfan057" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Thanks
>> >> > --
>> >> > cardfan057
>> >> >
>> >> >
>> >> > "Alan" wrote:
>> >> >
>> >> >> =IF(AND(A1>=500,A1<750),A1*0.05,IF(A1>=750,A1*0.1,0))
>> >> >> Regards,
>> >> >> Alan.
>> >> >> "cardfan057" <[email protected]> wrote in
>> >> >> message
>> >> >> news:[email protected]...
>> >> >> >I am making an invoice that will figure a discount for a certain
>> >> >> >amount
>> >> >> >of
>> >> >> > sale, all from the same cell, if the subtotal is < 500 then they
>> >> >> > will
>> >> >> > get
>> >> >> > no
>> >> >> > discount, if it is > 500 they will get a 5%, and if it is > 750
>> >> >> > they
>> >> >> > will
>> >> >> > get
>> >> >> > a 10% discount, what formula do i use??
>> >> >> > --
>> >> >> > cardfan057
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>