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?
Add a comment
|
1 Answer
=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, 2011 at 16:57
-
No, that's rounding to the nearest .95. Rounding down to the nearest .95 would be 2.95. Feb 4, 2011 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– CaffGeekFeb 4, 2011 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. Feb 4, 2011 at 17:01
-
-
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– CaffGeekFeb 4, 2011 at 17:04