0

I'm having trouble trying to create the formula to round numbers with an if function. I need to round decimals between 5 different statements:

  • If a number has decimals between 0.1 and 0.5 rounddown to 0.
  • If a number has decimals between 0.6 and 0.29 round to 0.25.
  • If a number has decimals between 0.30 and 0.55 round to 0.50.
  • If a number has decimals between 0.56 and 0.80 round to 0.75.
  • If a number has decimals between 0.76 and 0.99 round to 1.
3
  • Hi and welcome to WebApps. Would you please clarify some aspects of your question: you say If a number has decimals, so do you mean that you're only dealing with values between 0 and 1, can this be any number; e.g. 4.23, 6.95, etc. Second, there is a problem in the sequence of decimals values (maybe just a typo). "0.1 and 0.5": "0.5" must be wrong because the next level = 0.29. "0.6 and 0.29": "0.6" must be wrong because it is greater than 0.29". "0.76 and 0.99": do you mean "0.8 to 0.99"? Third, please give some examples of how a number should be rounded.
    – Tedinoz
    Mar 8 at 6:10
  • Please share a link to a spreadsheet with realistic sample data already entered, as well as your hand-entered results for what you'd like to see the formula returning. I agree with Tedinoz: your sets are off. I think you mean something like {0.01 - 0.05, 0.06 - 0.29, etc.}
    – Erik Tyler
    Mar 8 at 6:43
  • You are right. The secuence is: Mar 9 at 14:12

1 Answer 1

1

Use ifs(), like this:

=ifs( 
  A2 < 0.01, na(), 
  A2 <= 0.05, 0, 
  A2 <= 0.29, 0.25, 
  A2 <= 0.55, 0.5, 
  A2 <= 0.8, 0.75, 
  A2 <= 1, 1, 
  true, na() 
)
2
  • The OP's sequence of percentages is topsy turvy, but I wonder whether the first condition shouldn't be A2<0.01?
    – Tedinoz
    Mar 13 at 7:40
  • Edited the answer. Mar 13 at 21:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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