SOLVED

# Even numbers Roundup

Copper Contributor

# 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.

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

# Re: Even numbers Roundup

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

# Re: Even numbers Roundup

Does this do what you want?

=ROUND(A2+0.2,0)-0.01

# Re: Even numbers Roundup

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

# Re: Even numbers Roundup

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

# Re: Even numbers Roundup

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

# 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