• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

HELP - Interpolation between multiple data values across a table

G

GrahamF

New Member
Hi everyone,


I am trying to interpolate between multiple values across the X, and Y axis.

SO far it interpolates the rows perfectly but the columns it seems to repeat numerics.


I am using the Trend / Offset / Match criteria that Hui kindly provided.


I have indicated the error in red with calculation on the spreadsheet as attached link.


https://www.dropbox.com/s/h6mcxmkldxhq4yl/Trend-Offset-Match%20X%20%26%20Y%20axis%20problem.xls


Thanks, any help would be appreciated!!
 
Hi Graham ,


I think you need to do it in two stages ; first create a trend table where the x values go 1,2,3,4,.... ; in the second stage create the final trend table where the x values now go 11 , 12 , 13 , 14 ,....


I would upload a file showing this , but my Internet is not too good right now.


Narayan
 
Thanks Narayan,


I was thinking the same - but hoping there was a way to do it in one shot....can't win them all. :)
 
Hi Graham ,


Put the following formula in an intermediate table :


=TREND(B$2:B$11,$A$2:$A$11,$A22)


Copy this down and across to fill up your intermediate table.


Now , put the following formula in your final table cell B22 , and copy down and across :


=TREND($AA2:$AJ2,$B$1:$K$1,B$21)


I have put the intermediate table in the range AA2:AJ20.


Narayan
 
Hi Graham and Narayan. I believe this can be done with one megaformula, but it's very, very complicated.


I think I'm going to post this as a challenge in the Formula Challenge section. But before then, do you have any actual data Graham for this particular example that you could post so I can try my idea out? THe sample data above is a bit too basic. ANd can you give me a run-down as to what this is used for in real life?


The kind of thing I'm thinking of is say an experiment or a financial model with two independent variables, where you want to infer what would happen given any intermediate mix of those independent variables.


Come to think of it, when I put it that way it sounds exactly like a 2 variable regression model.
 
You must log in or register to reply here.
Back
Top