speed of formulas vs functions

[developer]

Hi everybody,

I'm just wondering if a function like =sum(a5:a15) is evaluated at the same
speed as a formula like =a5+a6+a7+a8+a9+a10+a11+a12+a13+a14+a15

I understand that if there is a performance difference it will not be
noticed with only one function or formula, but what happens when in a
workbook you have thousands of formulas or functions?

Any help is greatly appreciated.

greets,

Sybolt (at the office)

 

[Niek Otten]

HI Sybolt,

It is not difficult to test your example.
I did 4,193,408 calculations of both formulas and manually clocked them (of course I could have done that programmatically): 5.07
vs. 5.23 seconds. That is well within the margins of the accuracy of a manual timing, so it doesn't seem to make a lot of
difference.
From other experiments (not just by me) it is known that the number of formulas is more important than their complexity.
Of course the nature of the formulas does matter; VLOOKUPs can be very time consuming and User Defined Functions are, almost
without exception, very slow, compare to Excel's built in functions.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi everybody,
|
| I'm just wondering if a function like =sum(a5:a15) is evaluated at the same
| speed as a formula like =a5+a6+a7+a8+a9+a10+a11+a12+a13+a14+a15
|
| I understand that if there is a performance difference it will not be
| noticed with only one function or formula, but what happens when in a
| workbook you have thousands of formulas or functions?
|
| Any help is greatly appreciated.
|
| greets,
|
| Sybolt (at the office)
|
|

 

[MartinW]

Hi Sybolt,

I've always taken the approach that Excel still has to follow the
laws of mathematics, all of which can be calculated manually with
pencil and paper and an agile mind.

Excel has some great packages (functions) that will do all the repetitive
stuff and make life easy for us, however, at the end of the day it still
has to perform all the same calculations that you would do with a pencil.

I doubt that there would be any real difference between the two.

That is just my opinion. I don't have any technical evidence to back it up.

Cheers
Martin

 

[Bob Phillips]

There is one significant difference, parsing =SUM(A1:A10) quickly tells
Excel what the job to do is, whereas =A1+A2+A3+...+A10 takes more effort to
parse, as any one could be minus, times, a range, etc. However, having said
that. my timings are similar to Niek's. On average, the =SUM version was
quicker, but not significantly so.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[MartinW]

Yeah I see your point Bob.

To compare it to my pencil analogy,
you use more lead to write
=SUM(A1:A10)
rather than
=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10

Which would make virtually no difference in a small worksheet, however
could make quite a difference to an extremely large worksheet.

Thankfully, I only have to work with relatively small worksheets so I can
afford the luxury of saying "Just make it work and that will do".

Cheers
Martin

 

[Bob Phillips]

Yeah I see your point Bob.

To compare it to my pencil analogy,
you use more lead to write
=SUM(A1:A10)
rather than
=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10

LOL!

 

[sybmathics]

It is not difficult to test your example.
I did 4,193,408 calculations of both formulas and manually clocked them
(of course I could have done that programmatically): 5.07
vs. 5.23 seconds. That is well within the margins of the accuracy of a
manual timing, so it doesn't seem to make a lot of
difference.
From other experiments (not just by me) it is known that the number of
formulas is more important than their complexity.
Of course the nature of the formulas does matter; VLOOKUPs can be very
time consuming and User Defined Functions are, almost
without exception, very slow, compare to Excel's built in functions.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

OK, thanks,

I asked because the other I received a workbook of some 3,5 mb and every
time I changed only one value, it took about 20 secs before I could move on.

Inpsection of the workbok showed lots of formula's, so I wondered if I could
speed up the performance by changing form formulas to functions. But, as you
explained, apparently not.

cheers,

Sybolt

 

[Earl Kiosterud]

artin,

You're absolutely right. And it makes sense too, in most cases. Throwing CPU power and
memory at a solution seems extravagant, but modern software does that on a huge scale. And
it's relatively cheap. Look at the size of Windows, for example, and how much memory you
need for it to run without paging itself into a whirlwind. It wasn't that many years ago
that we had disks barely as large as todays memory. And at the end of the day, for most
problems, it isn't worth spending too much time trying to optimize a solution. The
exception, of course, is when the execution time gets large, or has to be done often, and
thus adds up.

 

[David McRitchie]

Something than can consume a lot of cycles involving use of
a lot of SUM formulas repeating the calculations above it,
such as filling down from cell B3 the formula =SUM(A$2:A3)
for a running total down a column.

at row 4000 you would see a lot more calculations
B4000: =SUM(A$2:A4000) for a running total down a column

than simply adding to the previous total above it, using either
B4000: =B3999 + A4000
or in a form better for insertion and deletion of rows
B4000: =OFFSET(B4000,-1,0) + A4000

http://groups.google.com/groups?as_umsgid=ugDxrXayCHA.2592@TK2MSFTNGP10

Slow Response
http://www.mvps.org/dmcritchie/excel/slowresp.htm