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

I want to calculate 3 different % from the same cell
 

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

I want to calculate 3 different % from the same cell
 

=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

I want to calculate 3 different % from the same cell
 

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

I want to calculate 3 different % from the same cell
 

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

I want to calculate 3 different % from the same cell
 

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

I want to calculate 3 different % from the same cell
 

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

I want to calculate 3 different % from the same cell
 

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

I want to calculate 3 different % from the same cell
 

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

I want to calculate 3 different % from the same cell
 

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

I want to calculate 3 different % from the same cell
 

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


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

I want to calculate 3 different % from the same cell
 

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

I want to calculate 3 different % from the same cell
 

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

I want to calculate 3 different % from the same cell
 

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


with @tiscali.co.uk


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


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