Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto range of numbers detection
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto range of numbers detection
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto range of numbers detection
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto range of numbers detection
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto range of numbers detection
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto range of numbers detection
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto range of numbers detection
In article . com,
"Aaron" wrote: 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. A bar graph may make it more obvious to you, but it doesn't address the fundamental problem. Both XL and VBA need specific, explicit criteria in order to calculate. There's no fuzzy logic engine built-in, which is what you're asking for. I do this in my head. And that's the issue - what you do in your head is obvious to you, but not obvious at all to a designer of an algorithm. One would need dozens of examples, encompassing the entire range of possible values, along with your judgment on how to group before one could design the kind of fuzzy logic analogue that you're using in your head. For example, based on the examples you've given it's entirely unclear to me, even if plotted as a bar graph, what rules to apply to decide whether this is one group or three: 0.001, 0.001, 0.002, 0.002, 0.001, 0.001 or whether this is two groups, or four, or six: 0.001, 20, 0.002, 525, 556.001, 524 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto range of numbers detection
On Feb 24, 4:34 am, JE McGimpsey wrote:
In article . com, "Aaron" wrote: 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. A bar graph may make it more obvious to you, but it doesn't address the fundamental problem. Both XL and VBA need specific, explicit criteria in order to calculate. There's no fuzzy logic engine built-in, which is what you're asking for. I do this in my head. And that's the issue - what you do in your head is obvious to you, but not obvious at all to a designer of an algorithm. One would need dozens of examples, encompassing the entire range of possible values, along with your judgment on how to group before one could design the kind of fuzzy logic analogue that you're using in your head. For example, based on the examples you've given it's entirely unclear to me, even if plotted as a bar graph, what rules to apply to decide whether this is one group or three: 0.001, 0.001, 0.002, 0.002, 0.001, 0.001 or whether this is two groups, or four, or six: 0.001, 20, 0.002, 525, 556.001, 524 Yes I suppose this is true, I was hoping that there might just be a formula or way of linking formulas that would be able to do what I would like to do. The errors that you have shown on the first example are typical of what I work with, but the second example would never happen. The calculations that lead me to the number in the 10 excel columns is a simple a minus b formula. (Error minus what it should be.) The errors therefore will be close to each other to start with and on a perfect calibration of equipment, there will be no errors at all. The information I know that could apply to all examples is stuff like: the exact amount of decimal places it will be, that they will all be the same decimal place, that the minimum division size in the error will be in a multiple of 0.1 or 0.5 for example, 0.1 0.2 0.3 0.1 or 0.5 0.15 0.10 etc not a mixture, And that the errors I get could be either positive or negative. For the examples you stated above, I would group the first 2, the second 2 and the last 2, making 3 groups. the second example would be 4 groups, the first number would be one, the second and the third would be another and the last 3 all in their own group. If you wrote them this way, 0.001, 0.002, 20, 525, 556.001, 524 then I would group the first 2 in one group, the third on its own and the last 3 together. Cheers, Aaron. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
two columns range of numbers need to list all numbers in the range | New Users to Excel | |||
A Challenge: Detection of first and Last +ve number in a range | Excel Worksheet Functions | |||
cannot disable hyperlinks auto-detection | Excel Discussion (Misc queries) | |||
Color detection | Excel Worksheet Functions | |||
I Need a Formula to Auto-fill Phone Numbers in a Range | Excel Worksheet Functions |