 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 I want to round them to 2.05 to 2.09, 2.15 to 2.29 and 2.25 to 2.29, 2.35 to 2.49 and 2.45 to 2.49 like even numbers in excel. So how can I do that?

Thank you.

6 Replies
best response confirmed by Akram79 (New Contributor)
Solution

# Re: Even numbers Roundup

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

# Re: Even numbers Roundup

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?

# Re: Even numbers Roundup

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.

# Re: Even numbers Roundup

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})

# Re: Even numbers Roundup

Thank you very much. Much Appreciate!