Finding equation and trendline from two variant table

**redbullah** · 03-07-2010, 03:38 PM

Hello everyone,

I am glad there is somewhere i can ask for help about excel. I will quickly ask my question, hopefully without any grammar mistake

As you see here, i have a table with two variants here. It is a table that I filled in with results of an experiment i did. For example according to this table "surface modulus of a shaft with 600 breaking strength and 20 surface roughness is 0,9"

better view is here

300 400 500 600 700 800 1000
5 1 0,99 0,9850,98 0,9750,9720,97
10 0,97 0,96 0,95 0,94 0,9350,9370,93
20 0,93 0,92 0,91 0,9 0,89 0,8850,88
30 0,91 0,9 0,88 0,86 0,84 0,82 0,78
50 0,8 0,76 0,67 0,61 0,56 0,51 0,43

Now i want excel to draw and find such an trendline and equation; when a person enters the information of breaking strength into a cell and surface roughness to another cell, excel shows the result of surface modulus in a cell. I know how to create trendlines and equations with r2 for simple one variant tables but, can you help me with this?

If you can't understand something becouse of my english, please ask, i am sorry and will answer

Thanks!

edit: thanks for fast reply shg, sure i can post a worksheet

here it is.New Microsoft Office Excel Worksheet.xlsx

**shg** · 03-07-2010, 03:44 PM

Welcome to the forum.

You can solve this with either a bilinear interpolation or a polynomial function. The polynomial is complicated; see http://www.excelforum.com/excel-gene...egression.html.

The bilinear interpolation could be done with formulas, but I use a UDF.

In either case, if you want help, please post a workbook rather than a picture.

**redbullah** · 03-07-2010, 03:55 PM

Originally Posted by shg

Welcome to the forum.

You can solve this with either a bilinear interpolation or a polynomial function. The polynomial is complicated; see http://www.excelforum.com/excel-gene...egression.html.

The bilinear interpolation could be done with formulas, but I use a UDF.

In either case, if you want help, please post a workbook rather than a picture.

thanks mate, i attached the worksheet in my first post

**shg** · 03-07-2010, 04:12 PM

Put this is a code module:

Please Login or Register  to view this content.

Then in some cell, for example,

=BiLinterp(550, 15, A1:H7) returns 0.925

**redbullah** · 03-07-2010, 04:22 PM

Umm, this looks very confusing to me. Thanks for the help tho, this is much complex then i thought

**redbullah** · 03-07-2010, 05:19 PM

ok im starting to understand it. but when i enter =BiLinterp command that you said, i recieve #VALUE error. sorry but im not too experienced in excel and vba coding, so i would be glad if you can help me patiently. thanks =)