Rounding Numbers - How to?

[mattstrachan]

I have a spreadsheet that gives measurements of items. The measurements are displayed to the nearest tenth. The problem I have is that the old spreadsheets are rounded to the nearest quarter. I need to compare these numbers. Does anyone have any suggestions on how best to approach this? Here is a quick example:

NewSpreadsheet has measurements 2.0, 2.1, 2.2, 2.3, 2.4, and 2.5.

I want to compare these numbers with the OldSpreadsheet that only has measurement options 2.0, 2.25, and 2.5.

So, I would like to make NewSpreadsheet measurements 2.0 and 2.1 equal 2.0, 2.2 and 2.3 equal 2.25, and 2.4 and 2.5 would equal 2.5.

I hope this makes sense. Thank you in advance for any suggestions and feel free to ask for more info if you need it.

[oeldere]

make a list with compearing measurements.

then you can work with VLookup.

[jake.masters]

Place this in the column one over from the measurements, if they were in A1, place this in B1.

Drag down:

=IF(OR(A1=2.0, A1=2.1), 2.0, IF(OR(A1=2.2, A1=2.3), 2.25, IF(OR(A1=2.4, A1=2.5), 2.5, "")

[mattstrachan]

thanks both of you

[Dionysos]

Rather than converting, how about evaluating the absolute value of the difference. If it is > .125 (half of a quarter), then it is not equal.

[Ace_XL]

How about..

=MROUND(A1,0.25)

[mattstrachan]

The MRound worked perfect. Simple and effective. Thank you.

[vlady]

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as "SOLVED".
For the meantime I'll do it for you.

How?
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

Note:
You can also thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given.
By doing so you can add to the reputation(s) of those who helped and shared their time in helping you.