Deriving a function/formula from a complete and an incomplete set of Data

[timmeh]

I am trying to build a little calculator which suits my personal needs. I want to avoid having to create huge tables of data manually and looking for corresponding values in the table from a given input using lookups. Ultimately I would like to avoid having to create the tables as a whole and only use reference values and let excel derive a formula/function for the rest of the values. This would save time as I would only have to feed function/formula the reference value. I am not trying to plot a graph!

Example 1, Complete set of data:

I have a table with Y-Values and corresponding X-Values. Everything has a linear behaviour except that the point of the 1.0 Mark changes depending on the Y- and X-Values.

So my question is if it is possible for Excel to derive a formula/function from such a set of data which can then be used in a calculation where the input solely consists of the Y- and X-Value and the formula/function will return the correct result? Furthermore I would like to know if I enter a value between say 11 and 12, would this derived formula be able to return the correct value? If not, then it would not be a big deal as I could just round or truncate the input.

Example 2, Limited set of data:

This is the same table as before except that I only have the reference values. Is it possible using those reference values only to derive a formula/function to either populate the table with the data from example 1 or to derive the a formula/function I am trying to get from example 1?

This would basically enable me to change the reference values on the fly and the formula just calculating the rest in the background which would result in much higher flexibility.

Thanks in advance for any pointers/help

-timmeh

[Richard Buttrey]

Hi,

Before we can decide whether Excel can automatically create a function we need to know what the relationship is between the X/Y values and the table values. You say it's linear but linear with reference to what?

Rgds

Rgds

[MarvinP]

Try sorting your data by Column B first.

Then look at the VLookup function.

If you wanted to return the 5 column given a value in B of 199 the formula would be
=VLOOKUP(199,$B$2:$V$75,5,TRUE)

Is that what you were looking for?

[shg]

You can put the sparse inputs in a 3-column table (X1, X2, Y), use LINEST to extract a bilinear (or other) fit, and then use those coefficients to reconstitute the balance of the table.

[timmeh]

Hi,

Before we can decide whether Excel can automatically create a function we need to know what the relationship is between the X/Y values and the table values. You say it's linear but linear with reference to what?

Rgds

Rgds

The Y-values represent the distance, the V-Values the elevation at a given point to be reached by an object, say a ball, rock or anything really. Assuming a input values Y and X, then X must be multiplied by the corresponding table-value to reflect it's "true value".

An example: Assuming the the distance is very low, and assuming the arch of the traveling object is very flat, each -1m elevation below you means significantly more in respect to your objects flightpath. Thus, the actual elevation of -1m must be multiplied by the table value to reflect the gain in distance you would achieve - the "true value". The reverse case is true if the distance is very high. The 1.0 mark represents the point where the object would gain 1m distance from the elevation for each 1m traveled.

The values are linear in respect to the minimum table value in each column to the 1.0 mark. and from the 1.0 mark to the highest value in the column. For X-value of 1:
1.50 -> 1.0 is linear and
1.0 -> 0.8 is linear

Try sorting your data by Column B first.

Then look at the VLookup function.

If you wanted to return the 5 column given a value in B of 199 the formula would be
=VLOOKUP(199,$B$2:$V$75,5,TRUE)

Is that what you were looking for?

If you are saying that if I input a value of 199 for Y and 5 for X then I would get the corresponding table-value, then yes, this would be a solution, but ultimately I am trying to avoid this. Thank you though

[shg]

From example2,

Please Login or Register  to view this content.

The formula in the blank cells, starting in B3, is

=$AA$2*$A3 + $AB$2*B$1 + $AC$2

And

Please Login or Register  to view this content.

[timmeh]

You can put the sparse inputs in a 3-column table (X1, X2, Y), use LINEST to extract a bilinear (or other) fit, and then use those coefficients to reconstitute the balance of the table.

I had a look at the LINEST function, it seems - from the description - it does what I want to do ultimately. However, I have no clue what exactly you are referring to or how I make use of the LINEST function to get what I want. I played around with it for a bit now, but I am completely incompetent in Excel