Limit algebraic function

[Terry Milnes]

I am trying to find a way to solve equations such as this: limit((SUMSQ(3,D1)+(12*D1)-8)/(SUMSQ(5,D1)-(11*D1)+6)). However, I cannot find a function for limit.

 

[James Ravenswood]

I am trying to find a way to solve equations such as this: limit((SUMSQ(3,D1)+(12*D1)-8)/(SUMSQ(5,D1)-(11*D1)+6)). However, I cannot find a function for limit.

Define "limit"

 

[joeu2004]

limit((SUMSQ(3,D1)+(12*D1)-8)/(SUMSQ(5,D1)-(11*D1)+6)).
However, I cannot find a function for limit.

Define "limit"

I interpreted Terry to mean
http://en.wikipedia.org/wiki/Limit_(mathematics).

AFAIK, there is no native Excel function that calculates the limit of an
arbitrary expression.

But Terry forgot to mention half of the requirement: the limit as D1
approaches what?

Terry did say "equations __such_as__ this". So perhaps an analysis of the
particular example is not useful. But just for completeness (and to satisfy
my curiosity)....

The first question is: is there even a limit for that expression?

I have forgotten my calculus. So I resorted to ad hoc means of analysis.

The particular example can simplified to:

y = (D1^2 + 12*D + 1) / (D1^2 - 11*D1 + 31)

There are no real roots for the denominator; that is, it is never zero for
any real values of D1.

On the other hand, there are 2 roots for the numerator; it is zero at about
D1=-0.08392 and D1=-11.91608.

Between the 2 roots, the graph of y is a skewed concave-up parabolic shape
with a single inflection point (change of direction) at about D1=-2.97975
and y=-0.35617.

I also discovered a concave-down parabolic shape with an inflection point at
about D1=5.5884 and y=131.0228.

So I was surprised to discover that y seems to be asymptotic to 1 for
D1<-11.91608 and D1>5.5884.

 

[joeu2004]

The particular example can simplified to:
y = (D1^2 + 12*D + 1) / (D1^2 - 11*D1 + 31) [....]
So I was surprised to discover that y seems to be asymptotic
to 1 for D1<-11.91608 and D1>5.5884.

The apparent asymptotic limits might be due to limitations of floating-point
calculations.

As I said, I have forgotten my calculus. So I am unable to analyze the
formula mathematically.

 

[joeu2004]

The particular example can simplified to:
y = (D1^2 + 12*D1 + 1) / (D1^2 - 11*D1 + 31) [....]
So I was surprised to discover that y seems to be asymptotic
to 1 for D1<-11.91608 and D1>5.5884.

The apparent asymptotic limits might be due to limitations of
floating-point calculations.

As I said, I have forgotten my calculus. So I am unable to analyze the
formula mathematically.

Well duh!, obviously y approaches 1.

As ABS(D1) becomes very large, 12*D1+1 and -11*D1+31 will become relatively
small and will have less influence. Thus, y approaches D1^2 / D1^2, which
is 1 of course.

(But it also true that floating-point limitations exacerbate and perhaps
accelerate the limit conditions.)

 

[Terry Milnes]

I am trying to find a way to solve equations such as this: limit((SUMSQ(3,D1)+(12*D1)-8)/(SUMSQ(5,D1)-(11*D1)+6)). However, I cannot find a function for limit.

Sorry! Been sick the last couple days. The limit was when D1 = -2 for this particular equation. However the very next question was when D1 = infinite, so Excel will not be able to handle this particular instance.

 

[joeu2004]

The limit was when D1 = -2 for this particular equation.

It does not make sense to speak of the limit of a formula with only one
variable __at__ a particular value of that variable.

You simply evaluate the formula by putting -2 into D1, and enter the
formula:

=(SUMSQ(3,D1)+(12*D1)-8)/(SUMSQ(5,D1)-(11*D1)+6)

Although as I stated in another response, this particular formula can be
simplified algebraically to:

=(D1^2 + 12*D1 + 1) / (D1^2 - 11*D1 + 31)

However the very next question was when D1 = infinite,
so Excel will not be able to handle this particular instance.

True, Excel cannot handle "infinity" per se. But you could do the best you
can by entering =SQRT(1E300) into D1.

Why SQRT(1E300)? Because D1^2 (D1 squared) is the dominate factor into both
the numerator and denominator, and 1E300 is close to the maximum value that
Excel can calculate.

The actual largest value that Excel can calculate is (2^1023 -
2^(1023-53))*2, which is about 1.7977E308. So SQRT(1E300) gives us enough
wiggle room to accommodate 12*D1, which is the next largest factor.

But as I explained in another response, as the magnitude of D1 becomes
large -- that is, +/-infinity -- you can use common sense to determine the
limit.

Again, as ABS(D1) becomes large, 12*D1+1 and 11*D1+31 become so small
relative to D1^2 that we can ignore them. Thus, __as__ ABS(D1) becomes very
large, the formula __tends__ toward =D1^2/D1^2, which of course is 1.

For example, when D1 is +/-SQRT(1E300), 12*D1+1 and 11*D1+31 are about
+/-1E151, whereas D1^2 is 1E300. That is, D1^2 is about 10^150 __times__
larger than 12^D1+1 and 11^D1+31.

It is important to note, first, that the denominator can never be zero with
"real" values of D1. See the quadratic equation for finding values of D1
when D1^2 - 11*D1 + 31 is zero. Refer to
http://en.wikipedia.org/wiki/Quadratic_equation.

It is also important to note that using Excel with large values in D1 is not
dispositive. The behavior of Excel calculations is limited by its
implementation of numbers, namely 64-bit binary floating-point. See
http://support.microsoft.com/kb/78113 for some explanation, albeit flawed in
some details.

It does not work in this case, but only because we did the mathematical
analysis first.

 

[joeu2004]

It does not work in this case, but only because we
did the mathematical analysis first.

Argh! I should have written: it __does__ work.

1E300 is close to the maximum value that Excel can calculate.
The actual largest value that Excel can calculate is
(2^1023 - 2^(1023-53))*2, which is about 1.7977E308.

I should have written: about 1.79769E308. 1.7977E308 is too large.

And I should have emphasized that that is the largest magnitude (plus and
minus) that can be __calculated__.

We cannot the number 1.79769E308 because Excel arbitrarily limits
__data_entry__ to about 9.99999E307. (See the "specification and
limitations" help page for the exact value.)

But if you do not want to enter the power-of-2 expression above, we can come
very close to it by entering the expression 1.79769E300*1E8.

I use 1E300 because I find it easier to remember ;-).

It is also important to note that using Excel with large
values in D1 is not dispositive. The behavior of Excel
calculations is limited by its implementation of numbers,
namely 64-bit binary floating-point. See
http://support.microsoft.com/kb/78113 for some explanation,
albeit flawed in some details.

To clarify.... Excel's representation of numbers -- the normal
representation in most computers -- is a "limited" set of discrete values
(if we can call about 1.8E19 values a "limited" set).

In contrast, mathematics works with an infinite continuous range of values.

The point is: some things are true using mathematics, but they are not true
using Excel arithmetic. For example IF(10.1 - 10 = 0.1, TRUE) is true
mathematically, but Excel returns FALSE(!).

So it is risky to use Excel to demonstrate a mathematical principle without
doing a mathematical analysis first.