Comparing displayed values

S

steev_jd

Hi,

I have to compare a very large worksheet, some of the numbers are onl
acurate to 3 decimal places, some of the others are accurate to abou
10 d.p.

I need to somehow shorten the numbers that are 10 dp down to 3 dp s
they will compare properly. If I go into format cells and adjust th
settings there all this does is shorten the amount of numbers shown o
the worksheet, the full number is still in the background and as suc
the MATCH formula returns a false.

Any ideas would be greatly appreciated.

Regards,
Stev
 
K

Kevin B

Changing formats merely modifies how the number is displayed, you can use
Round(Value,x...xx) to modify the actual value.

The following might be a possible solution, but save a copy of your original
file just in case:

Insert a helper column to the right of the column containing value with 3 or
more decimal positions

Assuming that the data is in column B row 1, enter the formula =ROUND(B1,3)
at the top of the new column and copy as far down as necessary

Copy the entire column of formulas

Select EDIT in the meny and select PASTE SPECIAL, and in the PASTE panel of
the dialog box, click the VALUES option button and click OK

Delete the column containing your formulas
 
Top