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

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

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

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

In article .com,
says...
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

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

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" wrote in message
om...
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

In article .com,
says...
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

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

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

In article ,
says...
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" wrote in message
om...
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

In article .com,
says...
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

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

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

"Tushar Mehta" wrote in message
om...
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

In article ,
says...
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" wrote in message
om...
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

In article .com,
says...
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

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

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 wrote:
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

"Tushar Mehta" wrote in message
om...
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

In article ,
says...
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" wrote in message
om...
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

In article .com,
says...
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

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

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

"Abe" wrote in message
oups.com...
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 wrote:
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

"Tushar Mehta" wrote in message
om...
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

In article ,
says...
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" wrote in
message
om...
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

In article .com,
says...
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