Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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.

share|improve this answer
    
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 – CaffGeek 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 – CaffGeek Feb 4 '11 at 17:04

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .