1
votes

I've been trying to find a list of the built in MS Excel functions that calculate "Whole Column" formula efficiently but haven't been successful, any ideas where I can go for this information? What i mean by this is exemplified below:

This documentation suggests that SUM and SUMIF formula automatically pick up on the last row of data, thus meaning that there is no efficiency reason why using a more restricted or dynamic range is preferable.

https://msdn.microsoft.com/en-us/library/office/ff726673(v=office.14).aspx#xlAllowExtraData

Answers for Excel 2003/7/10 are all welcome.

1
I don't see your point there. What the article means equals to Set rng = Intersect(rng, rng.Worksheet.UsedRange) in VBA. It is not necessary efficient either, if you have deleted a lot of rows/columns it is quite inefficient. There are quite a few discussions on how to find the last row and/or column in Excel more efficiently with the Range.Find function.user3819867
I believe only Array formulas (confirmed with CTRL + SHIFT + ENTER instead of just ENTER) are without "Whole Column formula efficiency", though I have never heard of that term and this is purely anequedotal. That is, Array formulas calculate the entire length of a column regardless of where data stops; I have no knowledge of whether other formulas act similarly.Grade 'Eh' Bacon
@user3819867 I'm aware that the End Row can be found more efficiently with Range.Find, that is not what I'm asking though. Do you have documentation to confirm that the intersect method is what Excel uses in these cases?Benjamin
I don't have documentation for it, only personal experience. If you want to create functions as efficient as the built-in ones, that will help you out in most cases.user3819867

1 Answers

1
votes

I think it would be fair to assume that ALL excel functions behave as teh article describes (ie the same as SUM and SUMIF). (I wasn't aware of this article, but it makes sense when you think about it...)

Behind the scenes the data in cells is stored in OO data structures such that only cells with non-default values and formatting will have been created.

It's highly probable that the value data and formatting data are help in separate containers.

So when Excel is using a formula on a range it is working on the data structures and consequently only works with the cells that have values.

I hope that a whole column with cells having different formatting (but very values) does not cause the SUM and SUMIF formulas to scan through every cell.

If in doubt you could do an experiment with the formulas you want to use.

The link you gave talked about formulas that behave differently and explicitly named VBA user created functions and array formulas - which makes sense.

Also, note that the article says using "Structured Table References" is the best approach. (ie not only storing your data in ranges but storing your data in excel tables. Created from a range using Ribbon:INSERT>Table).

These tables will allow any function to be used more efficiently as the range used is limited to the number of rows the table has.

I hope this helps.

Harvey