Results 1 to 10 of 10
Thread: long formula

20100905, 06:47 #1
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,760
 Thanks
 132
 Thanked 466 Times in 444 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

20100905, 07:35 #2
 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

20100905, 08:29 #3
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,760
 Thanks
 132
 Thanked 466 Times in 444 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

20100905, 09:08 #4
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,760
 Thanks
 132
 Thanked 466 Times in 444 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

20100905, 09:20 #5
 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

20100905, 09:29 #6
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,760
 Thanks
 132
 Thanked 466 Times in 444 Posts
Many thanks Andrew
When I get more time, I'll test both methods.
zeddy

20100906, 07:16 #7
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 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

20100906, 08:28 #8
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,760
 Thanks
 132
 Thanked 466 Times in 444 Posts
Hi Rory
Re: One of my formulas is:
=BH67 + DH67 + FH67 + HH67 + JH67 + LH67..
This formula will be copied across a span of 36columns, 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

20100906, 09:45 #9
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,270
 Thanks
 3
 Thanked 187 Times in 173 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

20100906, 13:59 #10
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 2,760
 Thanks
 132
 Thanked 466 Times in 444 Posts
Aha!
Yes, these 'blocks' span 36months and each will have a mmmyyyy header.
zeddy