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


with @tiscali.co.uk


"cardfan057" wrote in message
...
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" wrote in message
...
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


with @tiscali.co.uk


"cardfan057" wrote in message
...
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


with @tiscali.co.uk


"Alan" wrote in message
...
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" wrote in message
...
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


with @tiscali.co.uk


"cardfan057" wrote in message
...
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" wrote in message
...
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


with @tiscali.co.uk


"Alan" wrote in message
...
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" wrote in message
...
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


with @tiscali.co.uk


"cardfan057" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Thanks
--
cardfan057


"Alan" wrote:

=IF(AND(A1=500,A1<750),A1*0.05,IF(A1=750,A1*0.1, 0))
Regards,
Alan.
"cardfan057" wrote in message
...
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" wrote in message
...
Thanks
--
cardfan057


"Alan" wrote:

=IF(AND(A1=500,A1<750),A1*0.05,IF(A1=750,A1*0.1, 0))
Regards,
Alan.
"cardfan057" wrote in message
...
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


with @tiscali.co.uk


"cardfan057" wrote in message
...
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" wrote in message
...
Thanks
--
cardfan057


"Alan" wrote:

=IF(AND(A1=500,A1<750),A1*0.05,IF(A1=750,A1*0.1, 0))
Regards,
Alan.
"cardfan057" wrote in message
...
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