Math involved. Excel speed optimization question. UDF vs. longer excel equation

[Abe]

I want to create a function which defines a pulse wave that rises from
0 to 1, stays at one for a bit, lowers to 0, and stays at zero for a
bit.

/ if (t mod 1) <0.25, V(t) = 4* (t mod 1)
| if 0.5 >= (t mod 1) > 0.25, V(t) = 1
V(t) = |
| if 0.75> =(t mod 1) > 0.5, V(t) = 1-4* (0.75-(t mod 1))
\ if 1 > (t mod 1) > 0.75, V(t) = 0

For this pulse wave there are straight rises, falls and flat bottoms
and tops, which I need (and which is why a sine wave can't be used). If
you're still with me, thanks.

I can either put this all into a long excel equation for each cell that
I want this in (there will be about 4 'if' statements and 6 'mod'
statements) or create a UDF, where the t mod 1 only has to be
calculated once.

Any idea out there how much faster/ slower as UDF really is in a case
like this? Also, can you think of an easier way to create this pulse
wave?

As a side note, the t in my program has some ROW() and COLUMN()
dependence, and there will be 200+ cells filled with this equation
(thus, I can't just compute it once and copy the values to each cell).
Caculation speed is important because each cell is being calculated as
the time, t, is advanced by a timer.

Thanks in advance to anyone who takes a stab at this.

-Abe

 

[Tushar Mehta]

My guess would be that XL formulas should be very fast. Also, what you have
isn't overly complicated. I would go this route first. Further, I would
put t mod 1 (or, in XL, MOD(t,1)) into one cell. That way that expression
is calculated only once.

If you consider the UDF route, I would not be surprised if you do not see
any noticable performance difference. And, if you can generate the results
for a vector of values (t=0,0.05,...0.95, 1) all at once and return them as
an "array formula" the UDF will be quite fast.

You might also want to check your V(t) for 0.5 < t <= 0.75.
Maybe, you really want 1-4*(0.75-(t mod 1)) but it will go from V(0.5)=0 to
V(0.75)=1. If you are generating a 0-1 pulse with a linear rise and fall,
you need V(t)=4*(0.75-(t mod 1))

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Charles Williams]

I would suggest you work out a way of using CHOOSE (its fast and
non-volatile): this should be reasonably straightforward if your intervals
are equal (you have to be able to convert the interval test to a 1-based
integer).
It will be more concise than the equivalent IF and probably more efficient.

If you can sensibly do the calculation using an excel (non-array) formula
then the formula will almost certainly be faster than a UDF, particularly if
calculation is automatic or called by Excel rather than VBA.

regards
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

 

[Tushar Mehta]

I considered recommending CHOOSE but it requires INT(4*MOD(t,1))+1 to
calculate a number between 1 and 4. And, the OP would still have to do the
calculations in the individual choices anyways! So, why not just stay with
a nested IF structure?

As far as an XL formula being faster than a UDF goes, I know what the
conventional wisdom says. Yet, I have demonstrated on several occasions
that a properly constructed UDF (sometimes it needs to be array-aware) can
be faster than XL formulas (sometimes repeated in so many cells).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Charles Williams]

always interesting to test different ways of doing things:

=CHOOSE(4*MOD(A1,1)+1,4*MOD(A1,1),1,4*(0.75-MOD(A1,1)),0)
=IF(MOD(A1,1)<0.25,4*MOD(A1,1),IF(MOD(A1,1)<0.5,1,IF(MOD(A1,1)<0.75,4*(0.75-MOD(A1,1)),0)))

5000 CHOOSEs calculates in 5.8 millisecs
5000 IFs calculate in 8.6 millisecs

I absolutely agree with you that a well constructed UDF can be faster than
an equivalent formula, but in this case I think (although I have not tested
it <g>) that the overhead of 200 UDFs would be too high. A single array UDF
formula might win ...

regards
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

 

[Abe]

Thank you Tushar for catching the math error for V(t), I noticed it
when I ran the program.

Also, thank you charles for the speed test. What do you use to find out
how long it takes? ( I know its not a stopwatch

I had not thought of a UDF that calls the entire array, that's a great
idea and I will probably do that, it will be easy to impliment in my
code.

It's great to show up for work and have people giving good ideas and
feedback.

-Abe

 

[Charles Williams]

For time tests you can download my RangeCalc formula timer from
http://www.DecisionModels.com/downloads.htm

The array UDF method will be slow compared to formulae if you are
recalculating after each change in t because it will recalc all 200 cells
instead of just one, but if you change all 200 cells and then recalculate it
could be fast.
Note also that VBA UDF calculation time is much less if the Excel
calculation is directly initiated from VBA (application.Calculate etc)
rather than by automatic Excel calculation.

see http://www.DecisionModels.com/calcsecretsj.htm for hints on how to write
fast UDFs.

(I will update this page after my session at the London Excel Users
Conference).

regards
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com