SOLVED

Even numbers Roundup

Copper Contributor

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.

11 Replies
best response confirmed by Akram79 (Copper Contributor)
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

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?

@Akram79 

Please provide some more examples.

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.

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

Thank you very much. Much Appreciate!

@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

@Akram79 

Does this do what you want?

 

=ROUND(A2+0.2,0)-0.01

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

@Akram79 

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

Hi!

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

Thanks again
1 best response

Accepted Solutions
best response confirmed by Akram79 (Copper Contributor)
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

View solution in original post