| Welcome Guest, please sign in to participate in a discussion. | Search | Active Topics | |
Registered User
Joined: 3/7/2005
Posts: 23
|
Greetings!
I saw the following PCF in another thread:
100/(1+((H+L+C>H1+L1+C1)*(H+L+C)*V +(H1+L1+C1>H2+L2+C2)*(H1+L1+C1)*V1 +(H2+L2+C2>H3+L3+C3)*(H2+L2+C2)*V2 +(H3+L3+C3>H4+L4+C4)*(H3+L3+C3)*V3 +(H4+L4+C4>H5+L5+C5)*(H4+L4+C4)*V4 +(H5+L5+C5>H6+L6+C6)*(H5+L5+C5)*V5 +(H6+L6+C6>H7+L7+C7)*(H6+L6+C6)*V6 +(H7+L7+C7>H8+L8+C8)*(H7+L7+C7)*V7 +(H8+L8+C8>H9+L9+C9)*(H8+L8+C8)*V8 +(H9+L9+C9>H10+L10+C10)*(H9+L9+C9)*V9)/((H+L+C<H1+L1+C1)*(H+L+C)*V +(H1+L1+C1<H2+L2+C2)*(H1+L1+C1)*V1 +(H2+L2+C2<H3+L3+C3)*(H2+L2+C2)*V2 +(H3+L3+C3<H4+L4+C4)*(H3+L3+C3)*V3 +(H4+L4+C4<H5+L5+C5)*(H4+L4+C4)*V4 +(H5+L5+C5<H6+L6+C6)*(H5+L5+C5)*V5 +(H6+L6+C6<H7+L7+C7)*(H6+L6+C6)*V6 +(H7+L7+C7<H8+L8+C8)*(H7+L7+C7)*V7 +(H8+L8+C8<H9+L9+C9)*(H8+L8+C8)*V8 +(H9+L9+C9<H10+L10+C10)*(H9+L9+C9)*V9+.00001))
I understand that the .00001 is added to avoid the possibility of dividing by zero. But in a function that has a continuous range of possible values that includes both 0 and -0.00001, isn't it just as likely that the function's value will be -0.00001 as it is to be exactly zero? Is there some generic way, perhaps using the SGN() function, to ensure that division by zero cannot possibly happen?
Rob Richardson
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
There are several ways this could be done. I'll think about it some more, but every generic method I've thought of so far using TeleChart would involve reproducing a significant portion (if not all) of the denominator. I'm sure other people will read this topic and think about it as well.
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 3/7/2005
Posts: 23
|
Bruce,
Yes, I figured I'd have to reproduce the denominator. It's too bad we don't have a nice little IIF() method guaranteed to evaluate only one of its two expressions.
Rob
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
Here's the most straightforward method that comes to mind so far:
Replace: (denominator)
With: (denominator-(denominator=0)*.00001)
The constant is a bit arbitrary and not necessarily universal. It should be noted that while this could be useful under certain circumstances, it can add significantly to size of the formula. In addition, there probably aren't a lot of situations where it would produce significantly fewer divide by zero errors than just adding or substracting a carefully chosen constant to the denominator.
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
Registered User
Joined: 1/1/2005
Posts: 2,645
|
Notice that the PCF is of the form:
100/(1+N/(D+0.00001))
where U and D are nonpositive. In view of this, It was intended that the PCF be:
100/(1+N/(D-0.00001))
in order to prevent divide by zero.
Thanks,
Jim Murphy
|
|
Worden Trainer
Joined: 10/7/2004
Posts: 65,138
|
bustermu,
That was already changed in the original topic. I didn't think changing it in this one would make a lot of sense as it would involve editing more than the formula (although the thought crossed my mind). The conditions outlined in interrobang's hypothetical rather obviously don't apply to the formula in question anyway. Since there are strong educational advantages in pointing out the problem in the original formula, I'm glad you did.
-Bruce
Personal Criteria Formulas
TC2000 Support Articles
|
|
|
Guest-1 |