Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Variable size indexing (2002)

    I have a workbook that has worksheets (representing months) of differing number of rows of data. I need a generalized statement that will look at each sheet individually and based on a name (e.g. name=Sheet1!A1:A50 (where sometimes A50 is A100 or A220, etc.) I take data from another column. That part is easy.
    The issue I'm facing is that based on the "name" I want to pull and sum the first 10 data values (if there are 10), then 11-20 (if there are 11-20), then 21-50, then the balance, if any. The number of data values I'm extracting says the same (i.e., 10, 10, etc.), but the number of rows in each sheet varies from month to month, and there may be a case where name1 only has 8 values to extract and sum, or maybe name2 has 16 values -- so I need the sum of the first 10, then the sum of the next 6.
    Phew!

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Variable size indexing (2002)

    Not sure I completely understand, though (I think) I have a "general idea".

    How is the formula supposed to know how many rows to grab? Could you describe it in a little more detail and explain more what the formula you want is supposed to do?

    I am thinking along the lines of dynamic range name which can vary based on a count of rows, but you need some indication of the number of rows and where to start counting (just thinking out loud) perhaps with match on a different column to find the start and a countif on that value to get the number (assumes the data is sorted). The other alternative is an array formula, which can be set up like a multi-columned sumif.

    Sorry if I seem to be rambling, just wanted to make some comments before I lose them (the mind is the first to go). The exact answer and proicedures will depend on the details of the "setup".

    If need be, post an example workbook (with all proprietary info deleted) to help explain.

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Variable size indexing (2002)

    Thanks for the quick reply. I'll try to be clearer and provide more detail here.
    My main sheet is a summary sheet by name (rows) by period (columns). For a given name (row1, say), I need to match that name against the sheet in, say, January, column A (name column). So, for Steve's entries in January, I want his data from column B. In January, Steve might have 15 entries. I need to sum your data in column B for the first 10 entries and apply a percent against that sum; then, I need to sum your entries from 11-20, and apply a percent against that sum; then, sum entries from 21-30; and so, on. Note, however, in my example, Steve only had 15 entries in January. Therefore, the first 10 are summed and a percent applied against them, and the last 5 are summed and a different percent is applied against them. In February, Steve might have 40 entries...but the say idea applies!

    Clear as mud?

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Variable size indexing (2002)

    Based on the additional info, it seems like a pivot table (Data - pivot table report - answer the wizard) should be the way to go. You could use the "name" in the page field and then get a summary output for that name or use it in a field and get a summary of all.

    Again, if you give more details, we could provide more detailed step by step info (if desired).

    Steve
    Not clear on this, but do you have multiple identically formatted sheets (eg, Jan Feb, Mar, etc)? if so, I find it usually best to combine them into 1 sheet (add a new column of month to distinguish them). Revamping like this, gets you a host of "builtin routines and functions" (filtering, subtotals, grouping, etc). You can get excel to create an individual sheet (at runtime) relatively easily. Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15>

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: partition summation

    The problem I have w/a pivot table is getting ahold of the data for my summary sheet.
    I will try another approach at explaining it. I think it might better be called "partition summation".
    In a given month for a given person, I might have the following values: 10, 20, 10, 15, 20, 10, 15, 30, 40, 40, 40, 12, 50, 12, 15, 20
    and what I want is sum of the first min(10,#values) values -- i.e., sum(10, 20, 10, 15, 20, 10, 15, 30, 40, 40); then I want the sum of the next 10, if there are 10, and so on. I want to sum every group of 10 values.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: partition summation

    If thee is a column related to the "partition" just add it as a field and you will get partition summation. If nothing else add a calculated column which determines the partition (even based on the row# using the ROW() and MOD functions

    Steve

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: partition summation

    I wish it were this simple. I'm trying to partition the sums into groups of 10 for period 1. Then, once I'm in period 2 (separate sheet in the workbook), I need to look at the combination or concatenation of period 1 and period 2 for a given person, and group the concatenation into sets of 10 values that are to be summed.

    I'm really stuck. I suspect there's a VBA solution that I'm going to have to pour through to try to do this.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: partition summation

    I won't comment on whether you need a macro, formulaic, or a builtin feature. I don't feel I understand your setup enough to comment completely.

    As I mentioned before, if you provide a small example of what you have and an example of what you want an "output" or "outputs" to look like, we can better make suggestions. As it is, I think, I have a "little" understanding of what you have and what you want, so I can only make "suggestions" as to the approach and technique. I can't give recommendations.

    Based on the lack of anyone else chiming in with alternative suggestions, I would surmise that I am probably not alone in my lack of understanding.
    Steve

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: partition summation

    I'm attaching an example. Hope this helps more than my attempt to word it.
    Kevin

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: partition summation

    I am even more confused.

    Where does the 65, 75%, 85%, 95% come from?

    How are the "groupings" done? There is no indication from the setup or any explanation of the logic to any of the groupings that I can see?

    Steve

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: partition summation

    The percents are "locked in" percents that apply to the sum each group of 10. 65% of the first sum of 10, 75% of the second sum of 10, etc.
    The groupings are "simple" -- they're groups of 10 entries at a time...the first 10 get summed (and a percent take of those), the second 10, etc., etc.

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: partition summation

    Ahh, so the 10 is a total irrespective of period...

    I am still not clear what you are after, but I have a better idea.

    Do they have to be setup the way the are now?
    3 periods of 2 columns each?

    It would make more sense (at least to me) and make the calcs easier: if you made just 3 columns (irrespective of periods)
    Name
    Period
    amount

    Now the question is on sorting
    Will they always be sorted by name?
    If they are setup as I suggest (3 columns), would they be sorted by periods then name or by name, then periods or not at all?

    Can you use intermediate columns for intermediate calculations or must you just get a final answer?

    Steve

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: partition summation

    The attachement in the earlier post was just for illustration purposes. Yes, 10 is irrespective of the period. Each month I get a new set of data with any number of rows. I have a summary sheet on which I want to calculate the info by person by period. So, I need to run these calc for person-A, person-B, person-C, etc. (could be 50'ish people in total) in period 1, and get person-A's answer, then run it for person-B, etc.

    Then, in period 2, I need to run it again but have to look back to period 1 because that would tell me which block of 10 I'm in when I run this to apply the respective percentage. First block of 10 (whenever that happens) is 65% of the sum of a data column in the period 1 sheet. Second block of 10 (whenever that happens) is 75% of the sum of the data column, etc.

    Make sense?

    If it will help any, the people are selling a product. They're being commissioned based on how many sales they make. There is clearly an incentive to make MORE sales even if they're small sales...

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: partition summation

    You didn't answer my questions on the setup
    Can your data be setup differently?
    Could you run a macro on import to put it into a more useful form? Will it be sorted etc?
    The formula that will have to be created will depend on how the data is setup. Much of the problems you are having in trying to make formulas work (without code, so no UDF) is because the setup is not "logical" for what you want to calculate.

    What leeway do you have for making an intermediate sheet with the data setup more logically?

    Steve

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: partition summation

    I'm handed these monthly files (they actually come from another application that generates the Excel sheet that feeds my workbook). Once I get the sheet into my workbook, there's no problem creating another sheet that would extract the original data, sort it, etc., then work from that new sheet. I suppose that once I get the sheet for a given period, with a macro I could have Excel create another sheet, sort the data in that sheet, etc. Not a problem.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •