If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
I want to calculate 3 different % from the same cell
=IF(AND(A1=500,A1750),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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
I want to calculate 3 different % from the same cell
Thanks
-- cardfan057 "Alan" wrote: =IF(AND(A1=500,A1750),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 |
#6
|
|||
|
|||
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,A1750),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 |
#8
|
|||
|
|||
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,A1750),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 |
#9
|
|||
|
|||
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,A1750),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 |
#10
|
|||
|
|||
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 |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Any way to copy two paragraphs from one cell to multiple cells? | Kelly | Tables | 10 | March 6th, 2006 03:26 PM |
Copy cell format to cell on another worksht and update automatical | kevinm | Worksheet Functions | 21 | May 19th, 2005 11:07 AM |
In excel, the formula of a cell * by 5 then - 15 won't calculate . | mrsmers | General Discussion | 5 | October 24th, 2004 10:39 PM |
IF E3 & E10 = TRUE set this cell to "Yes", else set to "No" | Timothy L | Worksheet Functions | 5 | August 27th, 2004 02:28 AM |
Convert a Cell Reference to Text | Chuck Buker | Worksheet Functions | 6 | September 22nd, 2003 05:04 PM |