Search code examples
excelexcel-2007worksheet-function

Rounding numbers in complex conditions in Excel formula

I need to round up/down the numbers (last 2 digits, no decimal) based on three conditions:

  1. if 00, then -1
  2. if 01 to 49, then round up to 50
  3. if 51 to 98, then round up to 99

examples:

  • if the number is 1251, then it's 1299
  • if the number is 1298, then it's 1299
  • if the number is 4874, then it's 4899
  • if the number is 1433, then it's 1450
  • if the number is 1880, then it's 1899
  • if the number is 1301, then it's 1350
  • if the number is 1200, then it's 1299
  • if the number is 1250, then it's 1250 (unchanged)
  • if the number is 1299, then it's 1299 (unchanged)

..................

Solution

  • assume value is in a1

    =IF(MOD(A1,100)=0,A1-1,IF(MOD(A1,100)<51,A1+50-MOD(A1,100),A1+99-MOD(A1,100)))