455,611 Members | 1,541 Online 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
3 Replies

 P: n/a On 26 Oct 2005 00:08:31 -0700, "ghanley" 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 = 85075000 = ???50000 = 70033000 = ???10000 = 4008200 = ???1000 = 70345 = ???200 = 25I want to add a text box to a form - enter a value and press calculate.RegardsGary 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" 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 isderived, we couldn't suggest what method would be appropriate. I remembertaking a numerical analysis class in which, to point out the fallacy ofassuming the missing values could be calculated with whatever method we werelearning, the teacher gave a similar list with some missing values. Itturned out that no one's suggested method would work... it was a list ofroom 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 norchairs) and one storage room that was stacked, jammed-full, floor to ceilingwith extra folding chairs (which contained all the extra chairs for theentire 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 askfor more information. If it is some real-world situation, then ghanleyshould carefully consider if it is a problem that lends itself to acalculated 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. 