The both formulas, found below, provide the same result (if I copied the
formulas correctly). Which is more efficient?

Equation 1
=IF(I5*(1+GInt1)+H6<Input_Info!$C$22,I5*(1+GInt1)+ H6-Input_Info!$C$21,I5*(1+GInt1)+H6)

Equation 2
=I5*(1+GInt1)+H6-Input_Info!$C$21*(I5*(1+GInt1)+H6<Input_Info!$C$22 )

I would say equation 2 because:
-fewer keystrokes to input
-reduces the level of nested functions


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Brad" wrote:

The both formulas, found below, provide the same result (if I copied the
formulas correctly). Which is more efficient?

Equation 1
=IF(I5*(1+GInt1)+H6<Input_Info!$C$22,I5*(1+GInt1)+ H6-Input_Info!$C$21,I5*(1+GInt1)+H6)

Equation 2
=I5*(1+GInt1)+H6-Input_Info!$C$21*(I5*(1+GInt1)+H6<Input_Info!$C$22 )

"Brad" wrote:
Which is more efficient?

It is unclear.

But it should be noted up-front that whether or not any performance
difference will be noticable in the grand scheme of things depends on how
much of the total computation time is spent on statements like Eq 1 or Eq 2.
Generally, you should use the statement that you and perhaps others will
understand better and can maintain over time (i.e. make changes in future).
Another concern might be nesting level limitations, if circumstances warrant
it.

You did not specify the Excel version. My observations are based on Excel
2003.

Assuming that by "efficient", you mean fewer operations, ostensibly Eq 2
should be more efficient because:

1. No function call.

2. I5*(1+GInt1)+H6 is computed fewer times (but see below).

3. The additional multiplication (expression * condition) should be less
costly than the function call.

With respect to #1 and #2 above, ostensibly, all of the arguments of a
function must be computed before calling the function. At least, that is
true of a UDF.

However, the internal IF() function is not implemented in Excel 2003 as a
real function. Excel evaluates the arguments of IF() only as needed. That
is verifiable by putting UDF calls into each of the 2nd and 3rd arguments,
the true and false expressions.

That should also ameliorate the difference noted in #3.

In any case, with respect to point #2 above, you could have written Eq 1
more efficiently to eliminate that difference. To wit:

=I5*(1+GInt1) + H6 -
IF(I5*(1+GInt1)+H6 < Input_Info!$C$22, Input_Info!$C$21, 0)

In actual measurements, it appears that Eq 2 is more efficient if the
condition is true, but Eq 1 and Eq 2 take about the same amount of time if
the condition is false.

Arguably, the greatest performance difference is about 19%. But in all
cases, we are talking about 10 to 12 microseconds (!) per formula.

Average times:

Eq1: true: 12.348 usec; false: 10.700 usec

Eq2: true: 10.039 usec; false: 10.709 usec

Those number are for my computer. YMMV.


----- original message ------

"Brad" wrote in message
...
The both formulas, found below, provide the same result (if I copied the
formulas correctly). Which is more efficient?

Equation 1
=IF(I5*(1+GInt1)+H6<Input_Info!$C$22,I5*(1+GInt1)+ H6-Input_Info!$C$21,I5*(1+GInt1)+H6)

Equation 2
=I5*(1+GInt1)+H6-Input_Info!$C$21*(I5*(1+GInt1)+H6<Input_Info!$C$22 )

PS....

I wrote:
In actual measurements, it appears that Eq 2 is more efficient if the
condition is true, but Eq 1 and Eq 2 take about the same amount of
time if the condition is false.

That conclusion and my numbers are based on Eq 1 as I "improved" it.

Interestingly, Eq 1 as it was written originally is better than Eq 2 in both
cases (true and false conditions) by 1.6 to 2.8%.

To summarize, average times:

OP Eq1: true: 9.880 usec; false: 10.411 usec

My Eq1: true: 12.348 usec; false: 10.700 usec

Eq2: true: 10.039 usec; false: 10.709 usec

Again, those number are for my computer. YMMV.


----- original message -----

"JoeU2004" wrote in message
...
"Brad" wrote:
Which is more efficient?

It is unclear.

But it should be noted up-front that whether or not any performance
difference will be noticable in the grand scheme of things depends on how
much of the total computation time is spent on statements like Eq 1 or Eq
2. Generally, you should use the statement that you and perhaps others
will understand better and can maintain over time (i.e. make changes in
future). Another concern might be nesting level limitations, if
circumstances warrant it.

You did not specify the Excel version. My observations are based on Excel
2003.

Assuming that by "efficient", you mean fewer operations, ostensibly Eq 2
should be more efficient because:

1. No function call.

2. I5*(1+GInt1)+H6 is computed fewer times (but see below).

3. The additional multiplication (expression * condition) should be less
costly than the function call.

With respect to #1 and #2 above, ostensibly, all of the arguments of a
function must be computed before calling the function. At least, that is
true of a UDF.

However, the internal IF() function is not implemented in Excel 2003 as a
real function. Excel evaluates the arguments of IF() only as needed.
That is verifiable by putting UDF calls into each of the 2nd and 3rd
arguments, the true and false expressions.

That should also ameliorate the difference noted in #3.

In any case, with respect to point #2 above, you could have written Eq 1
more efficiently to eliminate that difference. To wit:

=I5*(1+GInt1) + H6 -
IF(I5*(1+GInt1)+H6 < Input_Info!$C$22, Input_Info!$C$21, 0)

In actual measurements, it appears that Eq 2 is more efficient if the
condition is true, but Eq 1 and Eq 2 take about the same amount of time if
the condition is false.

Arguably, the greatest performance difference is about 19%. But in all
cases, we are talking about 10 to 12 microseconds (!) per formula.

Average times:

Eq1: true: 12.348 usec; false: 10.700 usec

Eq2: true: 10.039 usec; false: 10.709 usec

Those number are for my computer. YMMV.


----- original message ------

"Brad" wrote in message
...
The both formulas, found below, provide the same result (if I copied the
formulas correctly). Which is more efficient?

Equation 1
=IF(I5*(1+GInt1)+H6<Input_Info!$C$22,I5*(1+GInt1)+ H6-Input_Info!$C$21,I5*(1+GInt1)+H6)

Equation 2
=I5*(1+GInt1)+H6-Input_Info!$C$21*(I5*(1+GInt1)+H6<Input_Info!$C$22 )