I looking for help to solve my spreadsheet to return value of different cells being similar within say +/- 10%. Say that I have in column values 20, 20.5, 21, 21.5, 23, 23.6, 24,24.8, 25.4.. These values are quite close to each other and I am looking for function to for example recognize values 20;20.5;21;21.5 as being in range of 10% between min and max and I would like to return one the same value for each of them of my choice for example 21 or average value rounded up to nearest 0.5. Could anyone help me with most efficient function which could sort those numbers this way?
2
-
Sort the cells by value. The find the absolute value of the difference between adjacent cells to see if they're in that range.– DrMoishe PippikCommented Aug 27, 2023 at 3:51
-
Maybe simplets; color code the values, similar colors will be similar. Simplest: Take the Average or Median, then compare to that. More complex: Sort and compare within a "sliding window" over a few of the values... Many ways to do it, depending on requirements and wishes.– HannuCommented Aug 27, 2023 at 11:35
Add a comment
|