Back-calculating a Value which uses Rounding in its Calculation

[dragon324]

Hi guys, I've been struggling with the following problem. Basically I have this equation:

EQUATION 1:

Please Login or Register  to view this content.

Round is the usual excel function, VBA_CLng is a VBA defined CLng function (*definition at end). Now what I'm trying to do is that instead of the user giving B and D and then the excel calculating N, I want the user to give N and D, and the excel to back-calculate B. What I've done is:

EQUATION 2:

Please Login or Register  to view this content.

This basically ignores the rounding and CLng operations. Most of the answers, from my analysis around 60% of the values between 1-8000 for N, come out correct - by correct I mean that if I take the B answer I calculate using N and D, and then go through the first equation I will get the same N that I provided initially. But in the rest of the cases, I will get errors between the given N and the one I re-calculate using the back-calculated value of B (not large errors, in the range of +/-5 - but still). How can I take the rounding process and CLng into account in Equation 2 such that I will eliminate such errors? (Also, please note that I absolutely have to round in the way it's done in the first equation - I can't put more of those operations together and Round fewer times)


Appendix*

Please Login or Register  to view this content.

[joeu2004]

Please Login or Register  to view this content.

Round is the usual excel function, VBA_CLng is a VBA defined CLng function [...]. Now what I'm trying to do is that instead of the user giving B and D and then the excel calculating N, I want the user to give N and D, and the excel to back-calculate B.

I suspect it is not feasible. Think about it: there are an "infinite" (very large) number of values between 2.5 (-4.88E-15) and 3.49999999999999 (+4.88E-15) that round to 3. Given 3, how could we determine which value rounded to it?

In some contexts, we might be able to narrow the range of possible values. And given some constraints (e.g. "B must be an integer"), we might be able to choose the correct value from within the range.

But I think the several individual roundings makes it infeasible, even if you stipulate the constraints.

In the meantime, why do you use VBA CLng instead of Excel ROUND? Are you aware of the difference between VBA Round, which is the same CLng, and Excel ROUND?

VBA CLng and Round do banker's rounding. Was that your intent?

If so, I think VBA Round is more reliable than VBA CLng. The latter might cause a runtime error if the parameter exceeds the limits of type Long.

[Special-K]

Not feasible as joeu2004 said.

Rounding a number converts a range of values to ONE value.
Reversing this would produce a range of possible numbers, so you wouldn't know exactly whcih one produced the rounded value.

[dragon324]

I suspect it is not feasible. Think about it: there are an "infinite" (very large) number of values between 2.5 (-4.88E-15) and 3.49999999999999 (+4.88E-15) that round to 3. Given 3, how could we determine which value rounded to it?

In some contexts, we might be able to narrow the range of possible values. And given some constraints (e.g. "B must be an integer"), we might be able to choose the correct value from within the range.

But I think the several individual roundings makes it infeasible, even if you stipulate the constraints.

In the meantime, why do you use VBA CLng instead of Excel ROUND? Are you aware of the difference between VBA Round, which is the same CLng, and Excel ROUND?

VBA CLng and Round do banker's rounding. Was that your intent?

If so, I think VBA Round is more reliable than VBA CLng. The latter might cause a runtime error if the parameter exceeds the limits of type Long.

Okay thanks for your answer. Then I guess I'll let the back-calculation return a B value and then re-calculate a value of N according to the B close to the initially given N value. This shouldn't be too great of a deal for the user, as the error never is greater than +/-5. As for why I used CLng it's because I did want banker's rounding on that. This really is me building in excel a series of calculations that will later be moved to an Access database. I should have used VBA Round instead of the regular excel Round, but I was too lazy to write

VBA_Round(10.5% * B + 0.0001, 0)

to get rid of the banker's rounding for those. Regarding the CLng - I prefer the run-time error because there should be no reason to exceed limits of type Long. The result of the CLng function should only be numbers between 0 and 700 of the form: 0, 10, 20, ... , 680, 690, etc.

Regarding not being able to incorporate the rounding, that makes sense. Thanks for both of your help!