*HELP*

S

Sandy Mann

Hi Joe,

Not only that but my formula takes twice as long to calculate.

From various posts I have seen where it seemed to me that people were
suggesting that fewer function calls were better, I jumped to the conclusion
that calculations were faster than function calls.

However, it would seem that in this case at least that is not true. In
calculating a whole column of your formula 1,000 times, the VBA Timer
returned an average of 0.064203 & 0.068414 seconds on two runs whereas mine,
with no function calls, took 0.135188 & 0.136234 seconds

Replacing raising to a power with multiplication improved my result to
0.077914 & 0.077797 seconds, so even then it did not beat your IF() formula,
(and of course gave wring results but I was only interested in calculation
times).

mmmmmm....... There's more to spreadsheets than meets the eye....


--
Regards

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk
 
S

Sandy Mann

Actually, for the sake of the archives if for no other purpose, I think that
I was doing myself a disservice because my methodology in timing the
formulas was flawed.

I tested it again with only one formula and set of data. I used VBA to
switch cell J8 alternately between 100 & 200 with cell I8 static at 50 and
used the VBA Timer to return the time at the start and finish of a 100,000
cycle loop.

The results then were:

Joe's IF() formula: an average over 5 of the above runs of 39.17188 secs
My *sledgehammer* formula over a similar 5 runs of the above loops: 39.71875
secs

I was quite surprised at the variation in the times returned for Joe's
formula which varied from a max of 40.30469 secs to a min of 38.78125 secs
whereas my formula only varied by 0.6 seconds so it may be that trying to
time formulas the way I did it is still flawed.


--


Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk
 
J

joeu2004

Sandy said:
Actually, for the sake of the archives if for no other purpose, I think that
I was doing myself a disservice because my methodology in timing the
formulas was flawed.
Click to expand...

I think the issue of how to time Excel spreadsheet operations is an
interesting one. I was tempted earlier to post a follow-up with some
questions; and now I have some new comments. But I decided it might be
worthy of a new thread.

Be that as it may, I will say here.... I used to worry about
arithmetic performance back in the days when a "fast" microprocessor
had a nominal instruction time of 1 microsecond (pre-RISC) and
floating-point operations were implemented in software. But today, I
lean toward readability and maintainability -- a standard which must be
tailored to the audience -- unless and until I discover or believe that
performance is truly an issue. (Certainly not in this case.) Of
course, I try to find the most concise "readable and maintainable"
solution, but only because I believe it conveys the purpose of the
formula best.

In short: "Don't sweat the small stuff".


----- previous posting -----
 
Top