Hi,

I am hoping someone could point me int he right direction on a problem
I have.

I have a series of conditions that give me a range of cells say a1 to
a10 all with a number in them. The number could be positive or
negative number in each cell.

How can I make excel automatically look at the range of numbers and
divide them up into groups of best fit.

The groups of best fit is just a logical arrangement of a group of
similar numbers in the same area, so I cant take a1, a5 and a10 and
group those numbers if they are the same or close to each other value
wise, because the numbers need to be grouped together from its
immediate surrounding numbers.

EG

10
11
12
10
10
4
6
8
22
25


In the above example I would group the numbers thus:

the first 5 together as one group,
the next 3 as another group,
and the last 2 as the last group.

At the moment its the only manual thing I do in my spreadsheet, and it
would be nice to fully automate this.

Any advice appreciated,

Cheers,

Aaron.

Two questions:

1) what is your algorithm for "best fit"/"similar numbers"? Contiguous
values with a range of 4 or less? A step change of 6 or more between
values? Two digits vs. single digits?

2) By "group" do you mean the group and outline function, or something
else?



In article . com,
"Aaron" wrote:

Hi,

I am hoping someone could point me int he right direction on a problem
I have.

I have a series of conditions that give me a range of cells say a1 to
a10 all with a number in them. The number could be positive or
negative number in each cell.

How can I make excel automatically look at the range of numbers and
divide them up into groups of best fit.

The groups of best fit is just a logical arrangement of a group of
similar numbers in the same area, so I cant take a1, a5 and a10 and
group those numbers if they are the same or close to each other value
wise, because the numbers need to be grouped together from its
immediate surrounding numbers.

EG

10
11
12
10
10
4
6
8
22
25


In the above example I would group the numbers thus:

the first 5 together as one group,
the next 3 as another group,
and the last 2 as the last group.

At the moment its the only manual thing I do in my spreadsheet, and it
would be nice to fully automate this.

Any advice appreciated,

Cheers,

Aaron.

On Feb 23, 11:11 am, JE McGimpsey wrote:
Two questions:

1) what is your algorithm for "best fit"/"similar numbers"? Contiguous
values with a range of 4 or less? A step change of 6 or more between
values? Two digits vs. single digits?

2) By "group" do you mean the group and outline function, or something
else?

In article . com,



"Aaron" wrote:
Hi,

I am hoping someone could point me int he right direction on a problem
I have.

I have a series of conditions that give me a range of cells say a1 to
a10 all with a number in them. The number could be positive or
negative number in each cell.

How can I make excel automatically look at the range of numbers and
divide them up into groups of best fit.

The groups of best fit is just a logical arrangement of a group of
similar numbers in the same area, so I cant take a1, a5 and a10 and
group those numbers if they are the same or close to each other value
wise, because the numbers need to be grouped together from its
immediate surrounding numbers.

EG

10
11
12
10
10
4
6
8
22
25

In the above example I would group the numbers thus:

the first 5 together as one group,
the next 3 as another group,
and the last 2 as the last group.

At the moment its the only manual thing I do in my spreadsheet, and it
would be nice to fully automate this.

Any advice appreciated,

Cheers,

Aaron.- Hide quoted text -

- Show quoted text -

Contiguous values yes, but the range of 4 or less cannot be fixed as
the numbers for each calculation can differ in DP and in size. What
works for one range might not work for the next, which could be:

..004
..005
..0
..002
..005
..006
..008
..007
..003
..005

The deciding range limit in the above case as a human working it out,
would probably be

the first 2 numbers,
the second 2 numbers,
the following 4 numbers and
the last 2 numbers.

So 4 ranges.

It is interpreted on a case by case basis and as humans we can do it
straight away finding the best ranges. There are a maximum of 5
ranges, minimum of 1.

1 range would be all the numbers the same.

It is a hairy problem I know but I need a solution and it is beyond me
at the moment.

Abd when i say group I just mean in the literal sense that you have 4
groups or 4 ranges of numbers based on the 10 numbers presented. (as
in the above example)

In the first post, I had 3 ranges or groups of contiguous numbers.

Cheers,

Aaron.

It is a hairy problem I know but I need a solution and it is beyond
me at the moment.

It will likely be beyond anyone else, too, unless you can develop at
least a fuzzy algorithm that can be coded. You can't code a case-by-case
solution unless you write code for every possible case. I can understand
your breakdown of your second example, but I couldn't necessarily
predict it - I might well come up with 1 group or 5 - what made you
decide that .002 to .005 was a break, but .003 to .005 wasn't?

Abd when i say group I just mean in the literal sense that you have 4
groups or 4 ranges of numbers based on the 10 numbers presented. (as
in the above example)

But what does this mean in terms of XL? Your "literal sense" is actually
not literal at all. Do you want the cells marked in some way? Or an
array variable to contain the values in the group? Or something else
entirely? What should the output of the division of groups be?

In article .com,
"Aaron" wrote:

On Feb 23, 11:11 am, JE McGimpsey wrote:
Two questions:

1) what is your algorithm for "best fit"/"similar numbers"? Contiguous
values with a range of 4 or less? A step change of 6 or more between
values? Two digits vs. single digits?

2) By "group" do you mean the group and outline function, or something
else?

In article . com,



"Aaron" wrote:
Hi,

I am hoping someone could point me int he right direction on a problem
I have.

I have a series of conditions that give me a range of cells say a1 to
a10 all with a number in them. The number could be positive or
negative number in each cell.

How can I make excel automatically look at the range of numbers and
divide them up into groups of best fit.

The groups of best fit is just a logical arrangement of a group of
similar numbers in the same area, so I cant take a1, a5 and a10 and
group those numbers if they are the same or close to each other value
wise, because the numbers need to be grouped together from its
immediate surrounding numbers.

EG

10
11
12
10
10
4
6
8
22
25

In the above example I would group the numbers thus:

the first 5 together as one group,
the next 3 as another group,
and the last 2 as the last group.

At the moment its the only manual thing I do in my spreadsheet, and it
would be nice to fully automate this.

Any advice appreciated,

Cheers,

Aaron.- Hide quoted text -

- Show quoted text -

Contiguous values yes, but the range of 4 or less cannot be fixed as
the numbers for each calculation can differ in DP and in size. What
works for one range might not work for the next, which could be:

.004
.005
.0
.002
.005
.006
.008
.007
.003
.005

The deciding range limit in the above case as a human working it out,
would probably be

the first 2 numbers,
the second 2 numbers,
the following 4 numbers and
the last 2 numbers.

So 4 ranges.

It is interpreted on a case by case basis and as humans we can do it
straight away finding the best ranges. There are a maximum of 5
ranges, minimum of 1.

1 range would be all the numbers the same.

It is a hairy problem I know but I need a solution and it is beyond me
at the moment.

Abd when i say group I just mean in the literal sense that you have 4
groups or 4 ranges of numbers based on the 10 numbers presented. (as
in the above example)

In the first post, I had 3 ranges or groups of contiguous numbers.

Cheers,

Aaron.

On Feb 23, 11:56 am, JE McGimpsey wrote:
It is a hairy problem I know but I need a solution and it is beyond
me at the moment.

It will likely be beyond anyone else, too, unless you can develop at
least a fuzzy algorithm that can be coded. You can't code a case-by-case
solution unless you write code for every possible case. I can understand
your breakdown of your second example, but I couldn't necessarily
predict it - I might well come up with 1 group or 5 - what made you
decide that .002 to .005 was a break, but .003 to .005 wasn't?

Abd when i say group I just mean in the literal sense that you have 4
groups or 4 ranges of numbers based on the 10 numbers presented. (as
in the above example)

But what does this mean in terms of XL? Your "literal sense" is actually
not literal at all. Do you want the cells marked in some way? Or an
array variable to contain the values in the group? Or something else
entirely? What should the output of the division of groups be?

In article .com,



"Aaron" wrote:
On Feb 23, 11:11 am, JE McGimpsey wrote:
Two questions:

1) what is your algorithm for "best fit"/"similar numbers"? Contiguous
values with a range of 4 or less? A step change of 6 or more between
values? Two digits vs. single digits?

2) By "group" do you mean the group and outline function, or something
else?

In article . com,

"Aaron" wrote:
Hi,

I am hoping someone could point me int he right direction on a problem
I have.

I have a series of conditions that give me a range of cells say a1 to
a10 all with a number in them. The number could be positive or
negative number in each cell.

How can I make excel automatically look at the range of numbers and
divide them up into groups of best fit.

The groups of best fit is just a logical arrangement of a group of
similar numbers in the same area, so I cant take a1, a5 and a10 and
group those numbers if they are the same or close to each other value
wise, because the numbers need to be grouped together from its
immediate surrounding numbers.

EG

10
11
12
10
10
4
6
8
22
25

In the above example I would group the numbers thus:

the first 5 together as one group,
the next 3 as another group,
and the last 2 as the last group.

At the moment its the only manual thing I do in my spreadsheet, and it
would be nice to fully automate this.

Any advice appreciated,

Cheers,

Aaron.- Hide quoted text -

- Show quoted text -

Contiguous values yes, but the range of 4 or less cannot be fixed as
the numbers for each calculation can differ in DP and in size. What
works for one range might not work for the next, which could be:

.004
.005
.0
.002
.005
.006
.008
.007
.003
.005

The deciding range limit in the above case as a human working it out,
would probably be

the first 2 numbers,
the second 2 numbers,
the following 4 numbers and
the last 2 numbers.

So 4 ranges.

It is interpreted on a case by case basis and as humans we can do it
straight away finding the best ranges. There are a maximum of 5
ranges, minimum of 1.

1 range would be all the numbers the same.

It is a hairy problem I know but I need a solution and it is beyond me
at the moment.

Abd when i say group I just mean in the literal sense that you have 4
groups or 4 ranges of numbers based on the 10 numbers presented. (as
in the above example)

In the first post, I had 3 ranges or groups of contiguous numbers.

Cheers,

Aaron.- Hide quoted text -

- Show quoted text -

Ah yes, well what i do is I take the groups of numbers to another cell
and perform calculations on those numbers to give me an accuracy
statement. Its hard to explain id be happy to email you a screenshot
of what i do manually and what it does for me from there to help you
better understand it?

Would a screenshot help?

Im treating the numbers in a standard deviation sort of way, I see the
group of similar values and group them together accordingly. Ideally
but not critical, is that the lesser amount of groupings necessary the
better.

Cheers,

Aaron.

On Feb 23, 12:09 pm, "Aaron" wrote:
On Feb 23, 11:56 am, JE McGimpsey wrote:





It is a hairy problem I know but I need a solution and it is beyond
me at the moment.

It will likely be beyond anyone else, too, unless you can develop at
least a fuzzy algorithm that can be coded. You can't code a case-by-case
solution unless you write code for every possible case. I can understand
your breakdown of your second example, but I couldn't necessarily
predict it - I might well come up with 1 group or 5 - what made you
decide that .002 to .005 was a break, but .003 to .005 wasn't?

Abd when i say group I just mean in the literal sense that you have 4
groups or 4 ranges of numbers based on the 10 numbers presented. (as
in the above example)

But what does this mean in terms of XL? Your "literal sense" is actually
not literal at all. Do you want the cells marked in some way? Or an
array variable to contain the values in the group? Or something else
entirely? What should the output of the division of groups be?

In article .com,

"Aaron" wrote:
On Feb 23, 11:11 am, JE McGimpsey wrote:
Two questions:

1) what is your algorithm for "best fit"/"similar numbers"? Contiguous
values with a range of 4 or less? A step change of 6 or more between
values? Two digits vs. single digits?

2) By "group" do you mean the group and outline function, or something
else?

In article . com,

"Aaron" wrote:
Hi,

I am hoping someone could point me int he right direction on a problem
I have.

I have a series of conditions that give me a range of cells say a1 to
a10 all with a number in them. The number could be positive or
negative number in each cell.

How can I make excel automatically look at the range of numbers and
divide them up into groups of best fit.

The groups of best fit is just a logical arrangement of a group of
similar numbers in the same area, so I cant take a1, a5 and a10 and
group those numbers if they are the same or close to each other value
wise, because the numbers need to be grouped together from its
immediate surrounding numbers.

EG

10
11
12
10
10
4
6
8
22
25

In the above example I would group the numbers thus:

the first 5 together as one group,
the next 3 as another group,
and the last 2 as the last group.

At the moment its the only manual thing I do in my spreadsheet, and it
would be nice to fully automate this.

Any advice appreciated,

Cheers,

Aaron.- Hide quoted text -

- Show quoted text -

Contiguous values yes, but the range of 4 or less cannot be fixed as
the numbers for each calculation can differ in DP and in size. What
works for one range might not work for the next, which could be:

.004
.005
.0
.002
.005
.006
.008
.007
.003
.005

The deciding range limit in the above case as a human working it out,
would probably be

the first 2 numbers,
the second 2 numbers,
the following 4 numbers and
the last 2 numbers.

So 4 ranges.

It is interpreted on a case by case basis and as humans we can do it
straight away finding the best ranges. There are a maximum of 5
ranges, minimum of 1.

1 range would be all the numbers the same.

It is a hairy problem I know but I need a solution and it is beyond me
at the moment.

Abd when i say group I just mean in the literal sense that you have 4
groups or 4 ranges of numbers based on the 10 numbers presented. (as
in the above example)

In the first post, I had 3 ranges or groups of contiguous numbers.

Cheers,

Aaron.- Hide quoted text -

- Show quoted text -

Ah yes, well what i do is I take the groups of numbers to another cell
and perform calculations on those numbers to give me an accuracy
statement. Its hard to explain id be happy to email you a screenshot
of what i do manually and what it does for me from there to help you
better understand it?

Would a screenshot help?

Im treating the numbers in a standard deviation sort of way, I see the
group of similar values and group them together accordingly. Ideally
but not critical, is that the lesser amount of groupings necessary the
better.

Cheers,

Aaron.- Hide quoted text -

- Show quoted text -

Another way to look at it is, if you plot the data on a bar graph, the
groupings are easier to see. You will be able to form a trend between
contiguous numbers and make "groups" out of the numbers accordingly.

I do this in my head.

Cheers,

Aaron.