Formula required

[ghanley]

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

[Tom van Stiphout]

Re: Formula required

On 26 Oct 2005 00:08:31 -0700, "ghanley" <ghanley@hcsfix it.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.


[color=blue]
>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[/color]

[Larry Linson]

Re: Formula required

"Tom van Stiphout" wrote
[color=blue]
> 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).[/color]

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

[Chuck]

Re: Formula required

On Thu, 27 Oct 2005 03:36:33 GMT, "Larry Linson" <bouncer@localh ost.not> wrote:
[color=blue]
>"Tom van Stiphout" wrote
>[color=green]
> > 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).[/color]
>
>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
>[/color]
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
--