By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,611 Members | 1,541 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,611 IT Pros & Developers. It's quick & easy.

Formula required

P: n/a
How would I go about creating a formula to fill in the unknown values??

Is this possible using VB for Access.

100000 = 850
75000 = ???
50000 = 700
33000 = ???
10000 = 400
8200 = ???
1000 = 70
345 = ???
200 = 25

I want to add a text box to a form - enter a value and press calculate.

Regards

Gary Hanley

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 26 Oct 2005 00:08:31 -0700, "ghanley" <gh*****@hcsfixit.com> wrote:

That depends. I look at this as a Math problem, with some X,Y values,
and some additional X-values where we want to know the Y-value.
Typically you'd want to do some sort of interpolation. Perhaps
straight lines between the known points (linear interpolation),
perhaps a smooth line (e.g. a cubic spline).

Google will have a ton of info on these keywords.

-Tom.
How would I go about creating a formula to fill in the unknown values??

Is this possible using VB for Access.

100000 = 850
75000 = ???
50000 = 700
33000 = ???
10000 = 400
8200 = ???
1000 = 70
345 = ???
200 = 25

I want to add a text box to a form - enter a value and press calculate.

Regards

Gary Hanley


Nov 13 '05 #2

P: n/a
"Tom van Stiphout" wrote
That depends. I look at this as a Math
problem, with some X,Y values, and
some additional X-values where we want
to know the Y-value. Typically you'd want
to do some sort of interpolation. Perhaps
straight lines between the known points
(linear interpolation), perhaps a smooth
line (e.g. a cubic spline).


But, without more information on what kind of data it is and/or how it is
derived, we couldn't suggest what method would be appropriate. I remember
taking a numerical analysis class in which, to point out the fallacy of
assuming the missing values could be calculated with whatever method we were
learning, the teacher gave a similar list with some missing values. It
turned out that no one's suggested method would work... it was a list of
room numbers in the building and the number of chairs/seats in the room.

And, as the missing numbers were associated with things like broom closets,
other janitorial rooms, and storage rooms (which contained neither seats nor
chairs) and one storage room that was stacked, jammed-full, floor to ceiling
with extra folding chairs (which contained all the extra chairs for the
entire building)... there was no "method" that could be used to _calculate_
them.

If the ghanley's teacher gave this as an assignment, ghanley had best go ask
for more information. If it is some real-world situation, then ghanley
should carefully consider if it is a problem that lends itself to a
calculated solution.

Larry Linson
Microsoft Access MVP
Nov 13 '05 #3

P: n/a
On Thu, 27 Oct 2005 03:36:33 GMT, "Larry Linson" <bo*****@localhost.not> wrote:
"Tom van Stiphout" wrote
That depends. I look at this as a Math
problem, with some X,Y values, and
some additional X-values where we want
to know the Y-value. Typically you'd want
to do some sort of interpolation. Perhaps
straight lines between the known points
(linear interpolation), perhaps a smooth
line (e.g. a cubic spline).


But, without more information on what kind of data it is and/or how it is
derived, we couldn't suggest what method would be appropriate. I remember
taking a numerical analysis class in which, to point out the fallacy of
assuming the missing values could be calculated with whatever method we were
learning, the teacher gave a similar list with some missing values. It
turned out that no one's suggested method would work... it was a list of
room numbers in the building and the number of chairs/seats in the room.

And, as the missing numbers were associated with things like broom closets,
other janitorial rooms, and storage rooms (which contained neither seats nor
chairs) and one storage room that was stacked, jammed-full, floor to ceiling
with extra folding chairs (which contained all the extra chairs for the
entire building)... there was no "method" that could be used to _calculate_
them.

If the ghanley's teacher gave this as an assignment, ghanley had best go ask
for more information. If it is some real-world situation, then ghanley
should carefully consider if it is a problem that lends itself to a
calculated solution.

Larry Linson
Microsoft Access MVP

Larry is exactly right. Thers is no reasonable relationship between X and its
corresponding value of Y. A least spline that passes thru all the data points
looks weird. That said, it is possible to write a funftion generator that
doesn't look too bad and which will pass thru all of the given data points.
However, the calculated values of Y for values of X between the original data
points is pure fiction. I believe you have coppied the data incorrectly or
your instructor has thrown you a mickey.

75000 ~~ 806.2-----
33000 ~~ 592.0-----
8200 ~~ 382.7-----
345 ~~ 33.1-----

Chuck
--
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.