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

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 <1151955561.556661.157800@h44g2000cwa.googlegroups.com>,
Abraham.Olson@gmail.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
>
>

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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
news:MPG.1f13499f91cf891c98b3b0@msnews.microsoft.com...
> 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 <1151955561.556661.157800@h44g2000cwa.googlegroups.com>,
> Abraham.Olson@gmail.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
>>
>>

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 <uCTXF#unGHA.4172@TK2MSFTNGP03.phx.gbl>,
Charles@DecisionModels.com 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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
> news:MPG.1f13499f91cf891c98b3b0@msnews.microsoft.com...
> > 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 <1151955561.556661.157800@h44g2000cwa.googlegroups.com>,
> > Abraham.Olson@gmail.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
> >>
> >>
>
>
>

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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
news:MPG.1f13719397c8b4cb98b3b1@msnews.microsoft.com...
>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 <uCTXF#unGHA.4172@TK2MSFTNGP03.phx.gbl>,
> Charles@DecisionModels.com 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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
>> news:MPG.1f13499f91cf891c98b3b0@msnews.microsoft.com...
>> > 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 <1151955561.556661.157800@h44g2000cwa.googlegroups.com>,
>> > Abraham.Olson@gmail.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
>> >>
>> >>
>>
>>
>>

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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
> news:MPG.1f13719397c8b4cb98b3b1@msnews.microsoft.com...
> >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 <uCTXF#unGHA.4172@TK2MSFTNGP03.phx.gbl>,
> > Charles@DecisionModels.com 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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
> >> news:MPG.1f13499f91cf891c98b3b0@msnews.microsoft.com...
> >> > 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 <1151955561.556661.157800@h44g2000cwa.googlegroups.com>,
> >> > Abraham.Olson@gmail.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
> >> >>
> >> >>
> >>
> >>
> >>