interpolation chart in excel

Question

My data is about real estate lots: area and price. The data is not sorted. How can I create a chart so that I can see the correlation between area and price?

For example for:

500 1,000,000
450 900,000
650 1,500,000
450 800,000

I'd like to see a line where 450 is first with values 900,000 and 800,00, then 500 then 650

Answer 1

If you are looking for something visual:

Create an XY (scatter) chart with Area on the horizontal axis and Price on the vertical axis. Then right click on the series and add a trendline.

I think this method makes it hard to pick a new point, like an area, and try to find out what price the trendline predicts.

I would try a formula:

=FORECAST(InputCell,B1:B14,A1:A4)

Where InputCell is a a cell where you enter an area for which you want an interpolated price, B1:B4 are your prices, and A1:A4 are your areas.

Answer 2

You need to sort the data. Area is first, Price is second (that's one sort operation, not two ). Then you'll have:

450 800 000
450 900 000
500 1 000 000
650 1 500 000

Now you can apply a scatter(X,Y) where the lines are connected. As the first row has similar data, the line will go upward, so you may have to exchange the X and Y values.