MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Advanced Number Rounding

Posted by Davo on October 29, 2001 11:27 AM

A formula in cell A1 returns a value that is rounded to the second decimal point. You want to look that value up in a range of cells in a different worksheet of the same workbook and return that same value in cell A2 if it's present, else you want A2 to display the value from the other sheet's specified range that represents the closest rounded number in the range to the value in A1. For example, the value in A1 is 0.040. A range of cells on the second sheet contains the values 0.030, 0.070, and 0.090. You want A2 to display 0.030 because it's the closest rounded number to 0.040 (if 0.040 was in the range, you'd want that to appear in A2). Is there a formula for A2 that can accomplish this?

Posted by Aladin Akyurek on October 29, 2001 11:54 AM




where range is the range in a column of interest in the other worksheet.

Hope this is what you want.