software

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 lt; 500 then they will get no
discount, if it is gt; 500 they will get a 5%, and if it is gt; 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-((A1gt;=750) (A1gt;=500))*5%)

--
HTH

Sandy
In Perth, the ancient capital of Scotland

with @tiscali.co.ukquot;cardfan057quot; gt; wrote in message
...
gt;I am making an invoice that will figure a discount for a certain amount of
gt; sale, all from the same cell, if the subtotal is lt; 500 then they will get
gt; no
gt; discount, if it is gt; 500 they will get a 5%, and if it is gt; 750 they will
gt; get
gt; a 10% discount, what formula do i use??
gt; --
gt; cardfan057
=IF(AND(A1gt;=500,A1lt;750),A1*0.05,IF(A1gt;=750,A1*0.1, 0))
Regards,
Alan.
quot;cardfan057quot; gt; wrote in message
...
gt;I am making an invoice that will figure a discount for a certain amount of
gt; sale, all from the same cell, if the subtotal is lt; 500 then they will get
gt; no
gt; discount, if it is gt; 500 they will get a 5%, and if it is gt; 750 they will
gt; get
gt; a 10% discount, what formula do i use??
gt; --
gt; cardfan057
Sandy,
That is really good! Do I have this right? It took a while to realise how it
works,
1-((A1gt;=750) (A1gt;=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.

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

gt; =IF(AND(A1gt;=500,A1lt;750),A1*0.05,IF(A1gt;=750,A1*0.1, 0))
gt; Regards,
gt; Alan.
gt; quot;cardfan057quot; gt; wrote in message
gt; ...
gt; gt;I am making an invoice that will figure a discount for a certain amount of
gt; gt; sale, all from the same cell, if the subtotal is lt; 500 then they will get
gt; gt; no
gt; gt; discount, if it is gt; 500 they will get a 5%, and if it is gt; 750 they will
gt; gt; get
gt; gt; a 10% discount, what formula do i use??
gt; gt; --
gt; gt; cardfan057
gt;
gt;
gt;

Sandy's solution is far shorter, efficient and more elegant than mine,
Regards,
Alan.
quot;cardfan057quot; gt; wrote in message
...
gt; Thanks
gt; --
gt; cardfan057
gt;
gt;
gt; quot;Alanquot; wrote:
gt;
gt;gt; =IF(AND(A1gt;=500,A1lt;750),A1*0.05,IF(A1gt;=750,A1*0.1, 0))
gt;gt; Regards,
gt;gt; Alan.
gt;gt; quot;cardfan057quot; gt; wrote in message
gt;gt; ...
gt;gt; gt;I am making an invoice that will figure a discount for a certain amount
gt;gt; gt;of
gt;gt; gt; sale, all from the same cell, if the subtotal is lt; 500 then they will
gt;gt; gt; get
gt;gt; gt; no
gt;gt; gt; discount, if it is gt; 500 they will get a 5%, and if it is gt; 750 they
gt;gt; gt; will
gt;gt; gt; get
gt;gt; gt; a 10% discount, what formula do i use??
gt;gt; gt; --
gt;gt; gt; cardfan057
gt;gt;
gt;gt;
gt;gt;
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.ukquot;Alanquot; gt; wrote in message
...
gt; Sandy,
gt; That is really good! Do I have this right? It took a while to realise how
gt; it works,
gt; 1-((A1gt;=750) (A1gt;=500))
gt; converts the TRUE or FALSE results to 1 or 0 thus returning 1 or 2, then
gt; the *5% returns 5% for 1 and 10% for 2 and A1 is multiplied by that?
gt; Regards,
gt; Alan.
gt;
gt; quot;Sandy Mannquot; gt; wrote in message
gt; ...
gt;gt; You don't say what you want if the amount is exactly 500 or 750 but try:
gt;gt;
gt;gt; =A1*(1-((A1gt;=750) (A1gt;=500))*5%)
gt;gt;
gt;gt; --
gt;gt; HTH
gt;gt;
gt;gt; Sandy
gt;gt; In Perth, the ancient capital of Scotland
gt;gt;
gt;gt;
gt;gt; with @tiscali.co.uk
gt;gt;
gt;gt;
gt;gt; quot;cardfan057quot; gt; wrote in message
gt;gt; ...
gt;gt;gt;I am making an invoice that will figure a discount for a certain amount
gt;gt;gt;of
gt;gt;gt; sale, all from the same cell, if the subtotal is lt; 500 then they will
gt;gt;gt; get no
gt;gt;gt; discount, if it is gt; 500 they will get a 5%, and if it is gt; 750 they
gt;gt;gt; will get
gt;gt;gt; a 10% discount, what formula do i use??
gt;gt;gt; --
gt;gt;gt; cardfan057
gt;gt;
gt;gt;
gt;
gt;

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

SellingQuanity Amount
PricePurchasedPurchased
$5.25 2 $10.50
55.002110.00
105.994423.96
38.70277.40

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

590.77
7A.35$632.12

I am needing d16 to show 5% if e15 is gt;=500 and lt;750, and 10% if e15 is
gt;=750, I have e16 formulated = e15*d16
--
cardfan057quot;Alanquot; wrote:

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

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:

=((E15gt;=750) (E15gt;=500)*5%)

This will return 5% or 10% as appropriate

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

=E15*((E15gt;=750) (E15gt;=500)*5%)

Which will return 31.093 from your example.--
HTH

Sandy
In Perth, the ancient capital of Scotland

with @tiscali.co.ukquot;cardfan057quot; gt; wrote in message
...
gt; This is what I am doing, I hope this helps.
gt;
gt; Selling Quanity Amount
gt; Price Purchased Purchased
gt; $5.25 2 $10.50
gt; 55.00 2 110.00
gt; 105.99 4 423.96
gt; 38.70 2 77.40
gt;
gt; (e15) 621.86
gt; ( D16) 5% (e16) 31.09
gt;
gt; 590.77
gt; 7% 41.35
gt;
gt;
gt; $632.12
gt;
gt; I am needing d16 to show 5% if e15 is gt;=500 and lt;750, and 10% if e15 is
gt;gt;=750, I have e16 formulated = e15*d16
gt; --
gt; cardfan057
gt;
gt;
gt; quot;Alanquot; wrote:
gt;
gt;gt; Sandy's solution is far shorter, efficient and more elegant than mine,
gt;gt; Regards,
gt;gt; Alan.
gt;gt; quot;cardfan057quot; gt; wrote in message
gt;gt; ...
gt;gt; gt; Thanks
gt;gt; gt; --
gt;gt; gt; cardfan057
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Alanquot; wrote:
gt;gt; gt;
gt;gt; gt;gt; =IF(AND(A1gt;=500,A1lt;750),A1*0.05,IF(A1gt;=750,A1*0.1, 0))
gt;gt; gt;gt; Regards,
gt;gt; gt;gt; Alan.
gt;gt; gt;gt; quot;cardfan057quot; gt; wrote in message
gt;gt; gt;gt; ...
gt;gt; gt;gt; gt;I am making an invoice that will figure a discount for a certain
gt;gt; gt;gt; gt;amount
gt;gt; gt;gt; gt;of
gt;gt; gt;gt; gt; sale, all from the same cell, if the subtotal is lt; 500 then they
gt;gt; gt;gt; gt; will
gt;gt; gt;gt; gt; get
gt;gt; gt;gt; gt; no
gt;gt; gt;gt; gt; discount, if it is gt; 500 they will get a 5%, and if it is gt; 750 they
gt;gt; gt;gt; gt; will
gt;gt; gt;gt; gt; get
gt;gt; gt;gt; gt; a 10% discount, what formula do i use??
gt;gt; gt;gt; gt; --
gt;gt; gt;gt; gt; cardfan057
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt; gt;gt;
gt;gt;
gt;gt;
gt;gt;
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.
quot;Sandy Mannquot; gt; wrote in message
...
gt; Alan,
gt;
gt; Yes that's correct. Because Excel performs multiplication, (or division),
gt; before subtraction, (or addition), it multiplies 5% by 1 or 2 depending
gt; what
gt; is in A1 to give 5%, (0.05) or 10%, (0.1) which is then subtracted from 1
gt; to
gt; give 0.95 or 0.9 respectively which of course id 95% or 90%
gt;
gt; --
gt; Regards,
gt;
gt; Sandy
gt; In Perth, the ancient capital of Scotland
gt;
gt;
gt; with @tiscali.co.uk
gt;
gt;
gt; quot;Alanquot; gt; wrote in message
gt; ...
gt;gt; Sandy,
gt;gt; That is really good! Do I have this right? It took a while to realise how
gt;gt; it works,
gt;gt; 1-((A1gt;=750) (A1gt;=500))
gt;gt; converts the TRUE or FALSE results to 1 or 0 thus returning 1 or 2, then
gt;gt; the *5% returns 5% for 1 and 10% for 2 and A1 is multiplied by that?
gt;gt; Regards,
gt;gt; Alan.
gt;gt;
gt;gt; quot;Sandy Mannquot; gt; wrote in message
gt;gt; ...
gt;gt;gt; You don't say what you want if the amount is exactly 500 or 750 but try:
gt;gt;gt;
gt;gt;gt; =A1*(1-((A1gt;=750) (A1gt;=500))*5%)
gt;gt;gt;
gt;gt;gt; --
gt;gt;gt; HTH
gt;gt;gt;
gt;gt;gt; Sandy
gt;gt;gt; In Perth, the ancient capital of Scotland
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; with @tiscali.co.uk
gt;gt;gt;
gt;gt;gt;
gt;gt;gt; quot;cardfan057quot; gt; wrote in message
gt;gt;gt; ...
gt;gt;gt;gt;I am making an invoice that will figure a discount for a certain amount
gt;gt;gt;gt;of
gt;gt;gt;gt; sale, all from the same cell, if the subtotal is lt; 500 then they will
gt;gt;gt;gt; get no
gt;gt;gt;gt; discount, if it is gt; 500 they will get a 5%, and if it is gt; 750 they
gt;gt;gt;gt; will get
gt;gt;gt;gt; a 10% discount, what formula do i use??
gt;gt;gt;gt; --
gt;gt;gt;gt; cardfan057
gt;gt;gt;
gt;gt;gt;
gt;gt;
gt;gt;
gt;
gt;
gt;