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

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" <Abraham.Olson@gmail.com> wrote in message
news:1152107363.463125.163420@a14g2000cwb.googlegroups.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" <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
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>