formula in cell

saritaj · May 13th, 2002, 07:09 PM

Dear Mr. Excel,

Simple question for you but not for me, I am a basic excel user, I need to show a customer how much discount he can be given.

eg in one cell I will have the amount 3500 in the next column I want to show him the amount with the discount, how can I do this?

Sarita

elgringo56 · May 13th, 2002, 07:13 PM

say cell A1 equals the amount and the discount is 15%. In cell B1, put the formula =A1/1.15

Yogi Anand · May 13th, 2002, 07:31 PM

The discounted amount should be

=3500*(1-15%)

elgringo56 · May 13th, 2002, 07:36 PM

I assume by the way it is stated that the 3500 in one cell is an example and could be anything, deviding this by 1.15 will give the amount discounted by 15%

Yogi Anand · May 13th, 2002, 07:51 PM

Hi Sarita and elgringo56:
Let us look at it this way ...
If the item was not discounted, according to sarita, you would pay for the item $3500
Now, if Sarita is offering you a 15% discount, the discount is =3500*15%
so what would be the discounted price

=RegularPrice-Discount
=3500-(3500*15%) ... if the price of the item is in cell A1, then
=A1-(A1*15%)
=A1*(1-15%)
=A1*.85

Regards!

[ This Message was edited by: Yogi Anand on 2002-05-13 20:00 ]

PDuPre' · May 13th, 2002, 10:31 PM

1/1.15 equals 0.8696, not the desired 0.85, Easy mistake to make.
----------------
I have found it very useful to put the discount amount in a cell and link the formulas for the discounted amount to that cell.

For instance A1*(1-B1) Where B1 would equal 15 % or 0.15

This way when you print out the page you can visually check and see if the discounted amount was calculated correctly. With the discount hard coded on the formula you can't easily tell what the discount is supposed to be.

Philip

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 13th, 2002, 07:09 PM	#1
saritaj New Member Join Date: May 2002 Location: Sarita Johan Posts: 1	Dear Mr. Excel, Simple question for you but not for me, I am a basic excel user, I need to show a customer how much discount he can be given. eg in one cell I will have the amount 3500 in the next column I want to show him the amount with the discount, how can I do this? Sarita

May 13th, 2002, 07:13 PM	#2
elgringo56 Board Regular Join Date: Apr 2002 Location: Puerto Vallarta, Mexico Posts: 869	say cell A1 equals the amount and the discount is 15%. In cell B1, put the formula =A1/1.15

May 13th, 2002, 07:31 PM	#3
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	The discounted amount should be =3500*(1-15%)

May 13th, 2002, 07:36 PM	#4
elgringo56 Board Regular Join Date: Apr 2002 Location: Puerto Vallarta, Mexico Posts: 869	I assume by the way it is stated that the 3500 in one cell is an example and could be anything, deviding this by 1.15 will give the amount discounted by 15%

May 13th, 2002, 07:51 PM	#5
Yogi Anand MrExcel MVP Join Date: Mar 2002 Location: Michigan USA Posts: 11,452	Hi Sarita and elgringo56: Let us look at it this way ... If the item was not discounted, according to sarita, you would pay for the item $3500 Now, if Sarita is offering you a 15% discount, the discount is =350015% so what would be the discounted price =RegularPrice-Discount =3500-(350015%) ... if the price of the item is in cell A1, then =A1-(A115%) =A1(1-15%) =A1*.85 Regards! [ This Message was edited by: Yogi Anand on 2002-05-13 20:00 ]

May 13th, 2002, 10:31 PM	#6
PDuPre' Board Regular Join Date: Apr 2002 Location: Trussville, AL Posts: 134	1/1.15 equals 0.8696, not the desired 0.85, Easy mistake to make. ---------------- I have found it very useful to put the discount amount in a cell and link the formulas for the discounted amount to that cell. For instance A1*(1-B1) Where B1 would equal 15 % or 0.15 This way when you print out the page you can visually check and see if the discounted amount was calculated correctly. With the discount hard coded on the formula you can't easily tell what the discount is supposed to be. Philip