Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

If a value is less than 200, then it should be rounded up to 200.

How do you do this in Microsoft Excel?

share|improve this question
    
Do you mean if the number is between 1 - 200, it should return 200? Then use =ceiling(A1,200) (where a1 is either your number or the reference cell). This will round all numbers up by 200, so 201 will be 400. If that's not what you meant, edit your question to give some example conditions. –  jdh Jan 7 '13 at 3:42
    
if its more than 200, then it should be remain unchanged –  user184996 Jan 7 '13 at 4:00
    
I've created an overview of all answers given, so that you can see the result yourself: goo.gl/ryoYz –  Jacob Jan Tuinstra Jan 7 '13 at 5:25

3 Answers 3

Below formula helps

=MAX(A1, 200)
share|improve this answer
    
ok, updated.... –  neo Jan 7 '13 at 4:14
    
thank you so much –  user184996 Jan 7 '13 at 9:12
    
@user184996, you should accept the answer. –  Dane Jan 7 '13 at 21:07

Your requirement:

  • If a value is less than 200, then it should be round up to 200.
  • if its more than 200, then it should be remain unchanged

Solution:

=MAX(200, value)
share|improve this answer
    
thank you so much –  user184996 Jan 7 '13 at 9:12

If you wanted everything under 200 rounded to 200, and everything else left alone:

=IF(CELL<200, 200, CELL);

'CELL' being the coordinate.

If you literally wanted just the range from 1-200 to be rounded to 200, and not values less than 1, as your original question specifies:

=IF(cell<200, IF(cell<1, cell, 200), cell)

This will satisfy your stated requirements.

share|improve this answer
    
sorry but if it's more than 200 then it should remain as is –  user184996 Jan 7 '13 at 4:20
    
Yes, and both of these formulas(formulae) do that. Do a bit of work. –  Solemnity Jan 7 '13 at 4:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.