• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

which is faster?

D

dpowyslybbe

New Member
Hi all,

I have a spreadsheet with regularly changing data which I then need to perform a large number of addition/sum calculations on [approx 5000]. As a result speed of calculation is something in the back of my mind so I'd be interesting in knowing which addition method you think may be faster.

Method 1 - Use of the '+' symbol
- eg. =A1+C1+E1+G1

Method 2 - Use of the SUM function
- eg. =SUM(A1,C1,E1,G1)

Your thoughts would be much appreciated.

Yours,
 
Hi,

I would say that Method 1 calculates faster than Method 2 in your example. But to be sure, you can test the calculation time of a range, a sheet, or even a workbook. I find it very useful for heavy workbooks with a lot of fomulas to identify where calculation speed is the longest and should be improved.

You will find all information about excel caluclation, as well as the VBA code to calculate specific ranges recalculation speed, under the following link: http://msdn.microsoft.com/en-us/library/ff700515(v=office.14).aspx

Hope it helps!
Cheers,
 
Hi ,

My personal opinion is that Excel is disdainful of simple arithmetic operations ; even 5000 or 50000 operations , as long as they involve just the basic operations ( addition , subtraction , multiplication and division ) should be done in the blink of an eye.

It is the complex formulae involving functions such as INDEX , MATCH , VLOOKUP , INDIRECT , and too many others to mention , that take a lot of time.

Can you give an idea of how long it takes for a full recalculation ? Anything less than 5 seconds is not a situation where you need to think of optimization.

What is more pertinent is : which of the two is easier to enter ? The SUM formulae is easier to enter when it involves a contiguous range ; =SUM(A1:E1) is one thing , but to enter something like =SUM( A1 , J2 , K3 , B5 , C4 ) is not done ; it is far better to enter =A1 + B5 + C4 + J2 + K3

Narayan
 
Aurelie,
That link was brilliant, thank you. I can see me referring to it and number of times. The macro timers were really informative and... it doesn't really make a difference.

Narayan,
The spreadsheet is relatively small right now so full calculation is really quick, I was slightly concerned by what would happen as the spreadsheet grows into many thousands of lines. As a result I'll be using the
'A1 + B5 + C4 + J2 + K3' version, seems a bit neater in this instance. Thanks for your thoughts.
 
Hi,
If you hold down the Ctrl key while clicking a cell, Excel will automatically insert the delimiters between arguments in the SUM function.
For example, =SUM(A1, B1, C2, X3) can be setup by typing "=SUM(" and then Ctrl clicking the required cells or ranges. Once you have selected your cells or ranges, you ca press enter, and Excel would add the closing parenthesis. It might be easier than having to press the "+" key multiple times!

Cheers,
Sajan.
 
To test the speed I would setup some dummy data in 1,000,000 rows
then via a macro recalc the two formulas 10 times for each Formula using the timing routines above
In the macro, Disable screen updating and events before the run and re-enable after
Divide by 10,000,000 and Voila
Make sure all other programs are stopped and don't even move the mouse while it is calculating
 
@ Hui Sir, on a lighter note,

if the rows are 100,000, it will be next to impossible to write the formula in
"=A1+C1+E1+G1" or "=SUM(A1,C1,E1,G1)" fashion.

Only options remain will be "=SUM(A1:E1)" :D
 
@sachinbizboy
Hi, I don't get your point. but just in case... you're supposed to enter the formulas (any of them) in row 1 and then copy down... not enter them one by one. But I don't know if you were trying to mean that.
Regards!
PS: For 1048576 rows, all 4 columns with random values, runtime was less than 4-5 seconds.
 
@SirJB7 , Sir my doubt was, as Narayan Sir mentioned, "SUM formulae is easier to enter when it involves a contiguous range".

If the data is not in contiguos range and the user has to select all the cell manually (5000 rows as OP mentioned) it will take lot of time to complete the formula itselft !
 
@sachinbizboy
Hi!
I'm wondering how many time will both versions take in your computer to run. Could you perform the tests that Hui suggested? I tried to do it, but it took a few seconds for both cases, so it didn't deserve any analysis as NARAYANK991 wrote above.
Regards!
 
@SirJB7 , my assumption of the OP's issue is as follows.

He want to check what is the fast method between "=A1+C1+E1+G1" and "=SUM(A1,C1,E1,G1)"
The data contains 5000 ~ rows. As per the examples given by OP, I assume the data is not in a contiguous range. So he can not use a function such as Sum("A1:A5000").

I have tried to calculate the timings on this basis, I am aware if my assumption is wrong, my reply is invalid.
Before calculating the time, I had had a doubt in mind, if the user is taking 5000 combinations in his formula, how is he going to do it. Even the excel will not accept such formula as it will exceed character limit.

I am still confused on this part, my mistake though.
I could not understand 2 points from Hui Sir's post about how to check the timings.

1) recalc the two formulas 10 times for each Formula using the timing routines
2) re-enable after Divide by 10,000,000

I did not ask it before cause I do not want to trouble the experts on this.

Now about what I did to check the timings.

I created a dummy data in A1:A200 using randbetween.
Wrote three different formulas (present in the attachement).

Ran the code for 1200 time in Range E1, F1, and G1.
The formulas used were "=(A1+A2… till A200)", "'=SUM(A1,A2... till A200) and "=SUM(A1:A200)".

The first 2 formulas took 3 seconds for 1200 runs, 3rd took 1 second for same runs.

Also, I tried something with 100,000 rows of data.
Switched the excel in Manual mode.
In A1:A200 wrote a randbet formula, did not paste special it.
In H1:H100,000 pasted all the three formulas one by one.

Ran the line "ActiveSheet.Calculate" 10 times to check the timings.
The result were 1,2 and 3 seconds different formulas (details in the attachment).

I am aware my interpretation of the problem can be wrong so sorry for the confusion & such a long post. :)
 

Attachments

  • Fast and Furios 8.xlsm
    445 KB · Views: 1
Hi, sachinbizboy!

Good work. So the running time, such a few seconds in all cases don't deserve any further research, IMHO.

Regarding what Hui wrote, he's the kind of guy that tests things thoroughly, that's why he said 1 million rows, run 10 times (to avoid slight variations of memory status, caches, other processes, etc.) and then divide by 10 millions to get the time per unit.

In my case I filled 2 columns with 1 million rows.

And about this:
sachinbizboy said:
I am still confused on this part, my mistake though.
I could not understand 2 points from Hui Sir's post about how to check the timings.

1) recalc the two formulas 10 times for each Formula using the timing routines
2) re-enable after Divide by 10,000,000

I did not ask it before cause I do not want to trouble the experts on this.
Click to expand...
... (bold is mine) please do me a favor: whenever you have any doubt, would you please ask at the forums? Surely someone will be able to respond you. We're all here to learn and help, so there aren't any "experts" that shouldn't be disturbed with apparently trivial things: each issue has its own entity according to the person who's experiencing it.

Regards!

PS: Next time, stress your equipment with the roughest test you could afford to perform, if not 1 million, then 100K, lastly 10K, it'll all depend on the processing time. With simple and fast cases as this 200 rows don't become a deep and demanding test.
 
You must log in or register to reply here.
Back
Top