Excel, round down to nearest .95?

Question

I have a request to create a cell with a formula that is 50% of another cell, rounded down to the nearest .95 (basically it's a clearance price).

How can I do that in excel?

Mike Scott · Answer 1 · 2011-02-04 17:03:41Z

up vote 3 down vote accepted

=INT(<original val> / 2 + 0.05) - 0.05

Do you really want $3.89 to become $0.95? Because that's what rounding down to the nearest 0.95 does.

Also, note that for original prices of less than $1.90, this formula generates a price of $-0.05, which is what you asked for but is silly.

answered Feb 4 '11 at 16:57

	No, that's rounding to the nearest .95. Rounding down to the nearest .95 would be 2.95. – Dennis Williamson Feb 4 '11 at 16:58
	Yes, the problem I'm having is edge cases like 27.90, which should be 13.95, not 12.95. The formula I have so far is `=ROUNDDOWN(A3*.5, 0)-0.05` – Chad Feb 4 '11 at 16:59
	@Dennis half of $3.89 is $1.945. Rounding down to the nearest 0.95 gives $0.95. You can't round it to $1.95, because that would round up. – Mike Scott Feb 4 '11 at 17:01
	@Chad: the formula in my answer works fine for $27.90. – Mike Scott Feb 4 '11 at 17:02
	It's just to populate a worksheet with defaults. So the negative price shouldn't be an issue. I used ROUNDDOWN instead of int, as it's more descriptive to the action. But it's working. Thanks – Chad Feb 4 '11 at 17:04

feedback

1 Answer