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

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

HansVogelaar
MVP
May 26, 2023
Akram79

Does this do what you want?

=ROUND(A2+0.2,0)-0.01
- Akram79
  Copper Contributor
  May 26, 2023
  For example, I have multiple values and I want anything below 1.30, 2.30, 3.30, 4.30 etc to should go to 0.99, 1.99, 2.99, 3.99 respectively. And any thing above 1.30, 2.30, 3.30 etc should go to 1.99, 2.99, 3.99 respectively.
  
  Thanks
  - HansVogelaar
    MVP
    May 26, 2023
    Akram79
    
    If the formula that I suggested doesn't do what you want, please provide some examples of where it fails.