Even numbers Roundup

[Akram79]

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.

[#2 · Accepted solution]

@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

[#3]

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?

[#4]

@Akram79 

Please provide some more examples.

[#5]

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.

[#6]

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

[#7]

Thank you very much. Much Appreciate!

[#8]

@Hans Vogelaar 

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

[#9]

@Akram79 

Does this do what you want?

=ROUND(A2+0.2,0)-0.01

[#10]

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

[#11]

@Akram79 

If the formula that I suggested doesn't do what you want, please provide some examples of where it fails.

[#12]

Hi!

It actually worked today. Thanks a lot. That's the thing I wanted.

Thanks again