Saving calculation time on large workbook

thenextguy

Board Regular
Joined
Sep 19, 2004
Messages
72
I have a list of items and costs that is sliced and diced in numerous ways using sumproduct functions.

The list is updated each year and I'd prefer not to update my formulas each year, so I've tried to make the formulas dynamic. I'm using a COUNTA (example: =COUNTA(Sheet1!B1:B65536)) formula to keep track of the number of entries on the list and then using OFFSET formulas to adjust my ranges based on this numbers.

Okay, that's the setup. This question is this:

Is there an advantage of having only one COUNTA formula and then having my OFFSET formulas reference this cell. Example:

Code:
=SUM(OFFSET(Sheet1!A1,0,0,B1))
where A1 contains the aforementioned COUNTA formula.

Or should I just go ahead and build the COUNTA formula into OFFSET formula. Example:

Code:
=SUM(OFFSET(COUNTA(Sheet1!B1:B65536),0,0,B1))

I hope this makes sense. I'll be happy to try again if anything is unclear. Also, if there is a better way to do what I'm currently trying to do, I'm all for that, too! :)
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
offset() is volatile - lot's of them would impact workbook performance. replace with index() equivalent
 

thenextguy

Board Regular
Joined
Sep 19, 2004
Messages
72
offset() is volatile - lot's of them would impact workbook performance. replace with index() equivalent

Thanks for the reply, PaddyD. Can you explain a little further? I've only used index() to refer to one item in a range. How can I use it to resize a range?
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
as per the excel help file entry, index() can return a reference as well as a value.

if a1:a5 is populated by, e.g. row(), then

=sum(a1:index(a:a, count(a:a)))

would return 15 - or, if you had the count in b1:

=sum(a1:index(a:a, b1))
 

Watch MrExcel Video

Forum statistics

Threads
1,114,093
Messages
5,545,917
Members
410,711
Latest member
Josh324
Top