Number Grouping Formula or Macro
 

Hi,

On my spreadsheet I have a column with ten numbers. The numbers are
errors based on a calculation. What I manually do is look at the
numbers and divide them into 1 2 3 4 or 5 ranges.

I have the errors listed 1 through 10 as I have ten setpoints with
increasing weight range. (1gram to 10 grams)


My final statement from the errors is to say: "From 1 gram to 3 grams
the error is 1.4 grams, and from 4 grams to 6 grams the error is 1
gram, and 7 grams to 8 grams the error is 0.5 grams, and from 8 grams
to 10 grams the error is 0.2 grams.


I can have a maximum of 5 groupings of errors.

E.G.


1 0
2 1.4
3 1.2
4 1
5 1
6 1
7 0.5
8 0.5
9 0.2
10 0


In the above example I would manually make the groupings:


1-3 = 1.4
4-6 = 1
7-8 = 0.5
9-10 = 0.2


and in this case group the errors into 4 groups.


Another example:


1 0
2 0
3 0
4 0
5 0
6 1
7 1
8 1
9 1
10 1


I would make the groupings:


1-5 = 0
6-10 = 1


and in this case group the errors into 2 groups.


And one more example:


1 0.5
2 0.1
3 0.1
4 0.2
5 0.2
6 0.8
7 0.7
8 0.8
9 1
10 1.5


In this example I would group the numbers as such:


1 = 0.5
2-3 = 0.1
4-5 = 0.2
6-9 = 1
10 = 1.5


and in this case group the errors into 5 groups.


Can excel intuitively by formula, make this calculation and grouping
somehow?


Thanks for any advice,


Aaron.