Forum Discussion

Copper Contributor

Jun 08, 2022

Solved

Even numbers Roundup

Hey everyone, I have around thousands rows of data in excel and I want to do numbers to nearest even round. For instance, I have 2.05, 2.15, 2.25, 2.35, 2.45, 2.55, 2.65, 2.75, 2.85 and 2.95 numbers ...

excel

Formulas and Functions

office 365

HansVogelaar
Jun 08, 2022
Akram79

Does this do what you want?

Let's say the numbers are in A2 and down.

Enter the following formula in B2, then fill down:

=CEILING(A2-0.09,0.2)+0.09

Akram79

Copper Contributor

Jun 08, 2022

Thanks for reply, it's working. However, what if I need 1.75 to 1.99, 1.85 to 1.99 and 1.95 to 1.99. Do not need 1.89. after 1.69 I need 1.99. so is it possible?

HansVogelaar

MVP

Jun 08, 2022

Akram79

Please provide some more examples.

Akram79
Copper Contributor
Jun 08, 2022
For example. 1.05 to 1.09, 1.15 to 1.29, 1.25 to 1.29, 1.35 to 1.49, 1.45 to 1.49, 1.55, 1.65 and 1.75 to 1.79 and then 1.85 and 1.95 to 1.99.
after 1.49 it should be 1.79 and 1.99.
- HansVogelaar
  MVP
  Jun 08, 2022
  Akram79
  
  In B2:
  
  =INT(A2)+LOOKUP(MOD(A2,1),{0,0.1,0.3,0.5,0.8},{0.09,0.29,0.49,0.79,0.99})
  - Akram79
    Copper Contributor
    May 26, 2023
    HansVogelaar
    
    what can I do in this formula if I want go to nearest value. For example, anything below 1.30 should go to 0.99 and anything above 1.30 should go 1.99.
    
    Thank you