Forum Discussion

elaina0813's avatar
elaina0813
Copper Contributor
Dec 04, 2019
Solved

trying to create a calculator using incremental rounding

Hello,

I am trying to create a calculator for incremental rounding. 

For example if I have a cell with a value I was wondering if i can get excel to recognize the value and place it/round it in one of the below categories, whichever one it fits into to...

 

< 5  - express as 0  
 ≤50 - express to nearest 5  increment
 > 50  - express to nearest 10  increment

 

so if my value is 28, id like excel to round it to the nearest 5...30 but if that same cell was 72 I'd want it to round to the nearest 10...70

 

Thanks in advance for your help!

excel
Formulas and Functions
  • ChrisMendoza's avatar
    ChrisMendoza
    Dec 04, 2019

    elaina0813 -

     

    Will something like below work for you?

    //Formatted
=SWITCH(
  TRUE,
  B3 < 5, 0,
  B3 <= 50, MROUND(B3,5),
  B3 > 50, MROUND(B3,10),
  -1
)
//Unformatted
=SWITCH(TRUE,B3<5,0,B3<=50,MROUND(B3,5),B3>50,MROUND(B3,10),-1)

     

     

1 Reply

  • ChrisMendoza's avatar
    ChrisMendoza
    Iron Contributor
    Dec 04, 2019

    elaina0813 -

     

    Will something like below work for you?

    //Formatted
=SWITCH(
  TRUE,
  B3 < 5, 0,
  B3 <= 50, MROUND(B3,5),
  B3 > 50, MROUND(B3,10),
  -1
)
//Unformatted
=SWITCH(TRUE,B3<5,0,B3<=50,MROUND(B3,5),B3>50,MROUND(B3,10),-1)

     

     

Resources