two percentage discounts in one cell

[Roni]

i'm trying to discount a number twice in one formula.

$320,400-50% and then -57% from that.

HOW do I do this????

 

[Roni]

Yeah, but what's in G8, B8 and H8

G8 the number typed in by me $320,400
B8 is a percentage typed in by me 50%
H8 is a percentage typed in by me 57.97%

Somehow this is supposed to sum 59,955, but I can't seem to get there.

 

[Roni]

i answered that incorrectly:

Cell G8 IS a number $320,400
Cell B8 shows 50.00% in cell and 50% in formula bar (formatted custom percentage)
Cell H8 shows 55.97% in cell, and 55.97% in formula bar (formatted custom percentage)

Should I change the percentage formatting? I set it up so one only has to type in the number 50 to get a 50%, instead of having to type .5.

 

[Seti]

You need to use:

=G8*(1-B8)*(1-H8)

since you are trying to reduce the value by the B8 and H8 percentages.

 

[Anne Troy]

59000?

No way.

No matter how you slice it, half of 320,000 is 160,000

56% of 160,000 is about 90000

Nowhere NEAR 59000

 

[Roni]

THANK YOU THANK YOU THANK YOU.

IT WORKDED MARVERLOUSLY.

OH MY GOODNESS, I WAS NEARLY READY TO SCREAM

THANK YOU AGAIN.

 

[tusharm]

To see the actual number (rather than the formatted result) right-click the cell then Format Cells... | Number tab | select the General format.

To take a number, say x, and discount it *to* y% use x*y/100.

To discount the number *by* y percent use x*(1-y/100).

So, to discount a number in A1 by the value in B8 (assuming B8 has a number between 0 and 1), use =A1*(1-B8).

the number in G8 is a number (currency $320,400). From that number I need to subtract a percentage (which will change) that is formatted (I believe) as a percentage in cell H8, and then from that, I need to subtract another percentage (which will change) in cell B8.

 

[Mark W.]

To see the actual number (rather than the formatted result) right-click the cell then Format Cells... | Number tab | select the General format....

However, that too may be rounded if the column width is sufficiently small. Best to examine the contents of the Formula Bar.

 

[Roni]

SETI completely fixed my problems... I didn't know anything about ONE minus...still don't quite understand it, but it worked. Thank you Seti.

 

[simsy]

Jumping in at the end here, with a bit of an explanation for the one minus.....

reducing something BY 30% is very different to reducing it TO 30%...

Start with 100, reducing it BY 30% leaves you with 70,
i.e. 70%,
i.e. 100 *70%
i.e. 100 * (1-30%)

Start with 100, reduce it TO 30% leaves you with 30, a number very different to 70.

Uniquely, in the instance of 50%, reducing BY and reducing TO both leave you with 50.

Perhaps because your first percentage change was 50, an error in methodology was being missed?

I hope this helps?

Regards,

Simsy

 

[rrdonutz]

Methinks they got it right. This 'double-discounting' practice is common in the U.S., where department stores advertise 40% off sale prices, and then entice you with bonus 20% off coupons. Unfortunately, you don't get a $100.00 sweater for $40.00, but $48.00. Mathematically, the only way to arrive at that is SalePrice = OriginalPrice * (1-Discount1) * (1-Discount2). This is essentially what Seti offered above.

--Tom