I want to calculate 3 different % from the same cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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??
 
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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
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.
 
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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
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
 
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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
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 said:
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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
Click to expand...
 
When I apply =((E15>=750)+(E15>=500)*5%) and the total is over 750 it gives
me 105% instead of 10%.
 
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


: 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
:>
:> (e-mail address removed)
:> (e-mail address removed) with @tiscali.co.uk
:>
:>
:> :> > 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.
:> >> :> >> > Thanks
:> >> > --
:> >> > cardfan057
:> >> >
:> >> >
:> >> > "Alan" wrote:
:> >> >
:> >> >> =IF(AND(A1>=500,A1<750),A1*0.05,IF(A1>=750,A1*0.1,0))
:> >> >> Regards,
:> >> >> Alan.
:> >> >> :> >> >> >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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Back
Top