Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple problem complex solution?
Hi,
I have had this problem for quite a while and I couldnt get it resolved on here before but I am hoping someone might be able to sort it for me. Here is the situation: I have a column with 10 answer boxes in it. Each box displays an "error" in the form of a number. The boxes can have the same error or different errors or no errors. The errors are typically anything from 0.00001 to 1. I have a series of 5 more result boxes that I need to group the errors in their most logical groupings into. eg series 1 0 0.001 0 0.001 0 0 0 0.002 0.001 0 As a human looking at the numbers it is easy to group them logically as: First 4 numbers together, next 3 together, last 3 on their own each. (I dont have to populate all 5 result boxes) eg series 2 0 0 0 0 0 0 0 0.001 0.001 0 As a human looking at the numbers it is easy to group them logically as: First 7 numbers together, next 2 together, last 1 on its own. (I dont have to populate all 5 result boxes) Can this be done using code or is it better through complex nested if statements to decide the groupings? Thanks in advance. Aaron. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple problem complex solution?
On Wed, 8 Jul 2009 00:24:19 -0700 (PDT), Aaron
wrote: Hi, I have had this problem for quite a while and I couldnt get it resolved on here before but I am hoping someone might be able to sort it for me. Here is the situation: I have a column with 10 answer boxes in it. Each box displays an "error" in the form of a number. The boxes can have the same error or different errors or no errors. The errors are typically anything from 0.00001 to 1. I have a series of 5 more result boxes that I need to group the errors in their most logical groupings into. eg series 1 0 0.001 0 0.001 0 0 0 0.002 0.001 0 As a human looking at the numbers it is easy to group them logically as: First 4 numbers together, next 3 together, last 3 on their own each. (I dont have to populate all 5 result boxes) eg series 2 0 0 0 0 0 0 0 0.001 0.001 0 As a human looking at the numbers it is easy to group them logically as: First 7 numbers together, next 2 together, last 1 on its own. (I dont have to populate all 5 result boxes) Can this be done using code or is it better through complex nested if statements to decide the groupings? Thanks in advance. Aaron. In order to do this through code, you will need to set out the rules that you are using to decide on your groupings. For example, in your first set: -------------------------- 0 0.001 0 0.001 0 0 0 0.002 0.001 0 ---------------------------- there are other groupings than the one you proposed that could be seen as "logical groupings", depending on the logical rules you are using. --ron |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple problem complex solution?
On Jul 8, 11:08*pm, Ron Rosenfeld wrote:
On Wed, 8 Jul 2009 00:24:19 -0700 (PDT), Aaron wrote: Hi, I have had this problem for quite a while and I couldnt get it resolved on here before but I am hoping someone might be able to sort it for me. Here is the situation: I have a column with 10 answer boxes in it. Each box displays an "error" in the form of a number. The boxes can have the same error or different errors or no errors. The errors are typically anything from 0.00001 to 1. I have a series of 5 more result boxes that I need to group the errors in their most logical groupings into. eg series 1 0 0.001 0 0.001 0 0 0 0.002 0.001 0 As a human looking at the numbers it is easy to group them logically as: First 4 numbers together, next 3 together, last 3 on their own each. (I dont have to populate all 5 result boxes) eg series 2 0 0 0 0 0 0 0 0.001 0.001 0 As a human looking at the numbers it is easy to group them logically as: First 7 numbers together, next 2 together, last 1 on its own. (I dont have to populate all 5 result boxes) Can this be done using code or is it better through complex nested if statements to decide the groupings? Thanks in advance. Aaron. In order to do this through code, you will need to set out the rules that you are using to decide on your groupings. For example, in your first set: -------------------------- 0 0.001 0 0.001 0 0 0 0.002 0.001 0 ---------------------------- there are other groupings than the one you proposed that could be seen as "logical groupings", depending on the logical rules you are using. --ron Hi, Yes I know that is what makes it so hard to do it with code. It is interpretational and I know code isnt, it is precise there is no grey area. The groupings must be consecutive they cant be one from the first one from the last one from the middle, the patterns must be grouped together. One way would be to group all that are the same, searching for a pattern that starts at the first number ending with the last number from top to bottom of list. Just group "likes" together. In the above example it would make 8 groups. I could then use nested if's to narrow it to 5 from there I am sure. Is there a way to do this with code and output as many different groupings that it finds as per example above: 0 0.001 0 0.001 0 0.002 0.001 0 eg 8 logical groupings. Cheers, Aaron. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple problem complex solution?
On Wed, 8 Jul 2009 18:25:05 -0700 (PDT), Aaron
wrote: Hi, Yes I know that is what makes it so hard to do it with code. It is interpretational and I know code isnt, it is precise there is no grey area. The groupings must be consecutive they cant be one from the first one from the last one from the middle, the patterns must be grouped together. One way would be to group all that are the same, searching for a pattern that starts at the first number ending with the last number from top to bottom of list. Just group "likes" together. In the above example it would make 8 groups. I could then use nested if's to narrow it to 5 from there I am sure. Is there a way to do this with code and output as many different groupings that it finds as per example above: 0 0.001 0 0.001 0 0.002 0.001 0 eg 8 logical groupings. Cheers, Aaron. Sure. When you lay out rules that are understandable, such as "group all that are the same", then that can be done. Here is a macro that shows how this step can be accomplished. To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens. To use this Macro (Sub), <alt-F8 opens the macro dialog box. Select the macro by name, and <RUN. ============================ Option Explicit Sub Groupings() Dim rSrc As Range, c As Range, rDest As Range Dim i As Long Set rSrc = Range("A1:A11") Set rDest = Range("B1") 'clear destination range 'could be up to same size as source range Range(rDest, rDest.Offset(rSrc.Rows.Count - 1, 0)).Clear i = 1 For Each c In rSrc 'Check for first entry -- always the first group If c.Address = rSrc(1, 1).Address Then rDest(1, 1).Value = c.Value Else 'Not first entry, so check preceding entry for identicality If c.Value < c.Offset(-1, 0).Value Then rDest.Offset(i, 0).Value = c.Value i = i + 1 End If End If Next c End Sub =================================== --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
simple yet complex scenario - goal seek problem | Excel Worksheet Functions | |||
Seems simple but no solution so far | Excel Discussion (Misc queries) | |||
Simple, yet complex problem! Using results as new data during calculations? | Excel Worksheet Functions | |||
Trying to find out a solution for a complex formula | Excel Discussion (Misc queries) | |||
Hlookup or Vlookup problem? or wich other solution? Complex Proble | Excel Discussion (Misc queries) |