Dividing range of numbers automatically

[Slashman]

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.

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.

 

[Slashman]

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.

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.

Can nobody lend a solution for this problem? Is it impossible or just
very hard?

Cheers,

Aaron.

 

[Pete_UK]

I think the problem is that there seems to be no hard and fast rules.
In your first example, why isn't item 1 grouped in with items 9 and 10
- do you always have to take them in the sequence quoted?

The increments between your groupings are not linear, but seem to be
based on the largest value of a particular group - which comes first,
though, the grouping or the value?

Maybe if you can try to describe more clearly how you manually allocate
the items to the groups, then some flexible formula may emerge - I'm
not sure how "intuition" and "formula" can co-exist.

Hope this helps.

Pete

 

[Slashman]

Hi Pete,

Yes I know what you mean. As humans, we can logically deduce the answer
easily but try and get a machine to do a seemingly simple task can be
alot harder!!

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.

Therefore I cannot just group the small ones all together like you
suggested.

Hope this clears my situation up a little for you to help.

Cheers,

Aaron.