Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing displayed values
Hi, I have to compare a very large worksheet, some of the numbers are only acurate to 3 decimal places, some of the others are accurate to about 10 d.p. I need to somehow shorten the numbers that are 10 dp down to 3 dp so they will compare properly. If I go into format cells and adjust the settings there all this does is shorten the amount of numbers shown on the worksheet, the full number is still in the background and as such the MATCH formula returns a false. Any ideas would be greatly appreciated. Regards, Steve -- steev_jd ------------------------------------------------------------------------ steev_jd's Profile: http://www.excelforum.com/member.php...o&userid=33107 View this thread: http://www.excelforum.com/showthread...hreadid=529643 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing displayed values
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 -- Kevin Backmann "steev_jd" wrote: Hi, I have to compare a very large worksheet, some of the numbers are only acurate to 3 decimal places, some of the others are accurate to about 10 d.p. I need to somehow shorten the numbers that are 10 dp down to 3 dp so they will compare properly. If I go into format cells and adjust the settings there all this does is shorten the amount of numbers shown on the worksheet, the full number is still in the background and as such the MATCH formula returns a false. Any ideas would be greatly appreciated. Regards, Steve -- steev_jd ------------------------------------------------------------------------ steev_jd's Profile: http://www.excelforum.com/member.php...o&userid=33107 View this thread: http://www.excelforum.com/showthread...hreadid=529643 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Comparing displayed values
Top man, thanks! -- steev_jd ------------------------------------------------------------------------ steev_jd's Profile: http://www.excelforum.com/member.php...o&userid=33107 View this thread: http://www.excelforum.com/showthread...hreadid=529643 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
filtered values can displayed in certain cell | Excel Worksheet Functions | |||
Min values in a list of numbers | Excel Worksheet Functions | |||
Counting values and comparing them to a single cell | Excel Discussion (Misc queries) | |||
Excel Comparing Values | Excel Discussion (Misc queries) | |||
Second serie doesn't use X-as values | Charts and Charting in Excel |