Results 1 to 10 of 10

Thread: long formula

  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi All

    I currently have 6 data blocks.
    I am using a '7th' block to sum the data in these 6 data blocks.

    One of my formulas is:
    =BH67 + DH67 + FH67 + HH67 + JH67 + LH67

    I now want to insert another 4 data blocks.
    Which means I need to add another 4 terms in my sample formula.

    I don't want to use a custom function.
    Is there a more efficient way of writing this formula?
    Or do I simple add my required 4 extra terms?

    If I have a cell which specifies the number of data blocks I have (e.g. a value from 1 to 10), could I use a more efficient formula??

    Just asking.

    zeddy

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    For the consistent pattern here you could use

    =SUMPRODUCT((BH67:TH67)*(MOD(COLUMN(BH67:TH67),52) =8))

    This works because the Column Numbers being added always give 8 when used with MOD 52

    so Column BH is column 60 and 60 MOD 52 =8
    Column DH is column 112 and 112 MOD 52 is 8 etc

    The SumProduct Function then adds all the cells in the range where the MOD 52 of the Column Number is 8

    You can extend this idea over other ranges, IF the pattern is consistent.
    Andrew

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Andrew

    Brilliant!!
    Thumbs up from me.
    Thanks for reminding me about SumProduct.
    However, I'm getting a circular ref error which I'm now trying to trace the cause.
    Will get back to you.

    Many thanks again

    zeddy

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Andrew

    Sorted the circ ref issue.

    So, with the marvellous formula you suggested, I do indeed get my answer.

    Second question now relates to calc performace.
    I have estimated that I may need to 'copy' your sumproduct formula to approx 20,000 cells.
    From your experience, would I be better to use the elegant sumproduct formula, or go with the longer =a+b+c+..+j

    zeddy

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Without testing I would not know for sure, but I suspect that the SUMPRODUCT formula
    would calculate slower than the simple addition, because it has rather more do do.
    Hopefully someone can come up with the definitive answer.
    Andrew

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Many thanks Andrew

    When I get more time, I'll test both methods.

    zeddy

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    SUMPRODUCT will be slower. Do you have any sort of header row that you could use in a SUMIF formula?
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Rory

    Re: One of my formulas is:
    =BH67 + DH67 + FH67 + HH67 + JH67 + LH67..

    This formula will be copied across a span of 36-columns, and then 'down' in several 'blocks' (typically 4 or 5 rows per block) , through several hundred rows.

    I do have a few header rows available though.

    zeddy

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    What I mean is, is there an identiying header row for those columns? E.g. if you had "Data1" in cells BH1, DH1, FH1 etc. then you could use a SUMIF fomula using row 1 as the criteria.
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Aha!

    Yes, these 'blocks' span 36-months and each will have a mmm-yyyy header.


    zeddy

Posting Permissions

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