• 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.

Calculations optimally

B

bines53

Active Member
Hello everyone,

Which is better, use the functions built in, or if there is a choice, make the form formulas.
For example functions as =POWER You can do ^ ,or SQRT You can do ^0.5

Thank you !
 
Hi Bines,

All I can say that, when I used =A^B or =(M:N)^B along with =POWER(A,B) or =POWER(M:N,B) in both cases POWER is fast.

Here A, B are numbers and M:N is an array of numbers.

May be somebody will give more details on it.

Regards,
 
Bines

I just ran a small experiment

I ran a macro that placed 10^x and power(10,x) in A1:A64000
Then calculated the worksheet
I ran the test for x = integers from 1 to 10
ie 10^1, 10^2 ... 10^10 and power(10,1) ...Power(10,10)

I timed it to the millisecond using some smart code from Daniel Ferry

Then I ran the whole thing 3 times and averaged the results

The results show up as
Capture.PNG

The above times are 1000 x the actual times
Bold is the fastest Time

Surprsingly there is a small 2.41% speed increase by using Power(10,x) over using 10^x
What is also surprising is that this changed for different values of x

Excel Functions are highly optimised and so Power() pipping 10^x isn't surprising

However a 2.41% change between 0.01090 and 0.01064 seconds to calculate 64,000 calcs is hardly worth worrying about in real life
 
Last edited:
Thanks for the answers !

The answer surprises me about Power() !

Which language the Functions is written ?

Thank you !
 
My timing functions are in VBA but they use some API calls

Excel is mostly written in C++
 
Hi,

If there is a place that description, what functions they do not written in C++ .

Regards,

David
 
David

I doubt there is much difference in C or C++ functions and as a user we really shouldn't care

The smarts comes in highly optimsied code to achieve the functions results
Believe it or not there are numerical/computational techniques which can be used to achieve functions results which at first glance appear nothing like the function they are replicating
 
Hi Hui,

I understand, in recent times I have read your wonderful forum, hints that slow functions, such as
SUMPRODUCT ,ROW ,Apparently there are many of them,I do not know what language they are written .
I want to know, because each millisecond speed is crucial for my activity.
There is also, of course, additions to Excel ,XLL .
I think it's very necessary for everyone, rank the functions by speed.
I think it will make the company Microsoft, improve their product.

Regards,

David
 
You must log in or register to reply here.
Back
Top