Interpolating data in excel tabel with missing data

Dear colleague,

Excel is fantastic to solve several problems but this time I cann’t find a proper solution.
I have a table with 21 numbers two rows with x and y e.g.
nr. x y
20 5,5
19 5,4
18 5,3
17 5,2 0,18
16 5,1 0,25
15 5,0 0,30
14 4,5 0,30
13 4,0 0,35
12 3,0
11 2,0 0,65
10 1,0
9 0,9 0,80
8 0,8 0,75
7 0,7
6 0,6 0,70
5 0,5 0,68
4 0,4 0,65
3 0,3 0,64
2 0,2 0,93
1 0,1 1,10
0 0,0 1,20

Now I wish to make a table with 100 numbers in wish y is interpolated based on y in the given table.
Using many IF statements (in Dutch “ALS“) isn’t usefull since the number of statements is becoming too large and besides is a number is missing e.g. nr7 x=0,7 the y is 0.
Is there a solution?
Thanx for any help

With regards Onno


This is not working properly:
=
ALS(P12<$H$28;"Bodem";
ALS(P12<$H$27;$i$28;
ALS(P12<$H$26;$i$27;
ALS(P12<$H$25;$i$26;
ALS(P12<$H$24;$i$25;
ALS(P12<$H$23;$i$24;
ALS(P12<$H$22;$i$23;
ALS(P12<$H$21;$i$22;
ALS(P12<$H$20;$i$21;
ALS(P12<$H$19;$i$20;
ALS(P12<$H$18;$i$19;
ALS(P12<$H$17;$i$18;
ALS(P12<$H$16;$i$17;
ALS(P12<$H$15;$i$16;
ALS(P12<$H$14;$i$15;
ALS(P12<$H$13;$i$14;
ALS(P12<$H$12;$i$13;
ALS(P12<$H$11;$i$12;
ALS(P12<$H$10;$i$11;
ALS(P12<$H$9;$i$10;
ALS(P12<$H$8;$i$9;
ALS(P12<$H$7;$i$8;
))))))))))))))))))))))

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Here's the excel file.

How do you want to interpolate it?
There's too much data missing...
Untitled.gif

That's just the case
I have a column with data x and y
but sometimes a pair of x,y is missing.
So trend isn't working.
So I want to make a full table based on the given table with some x and y values.
The empty ones is just causing the problems you see.
For one time it''ts ok to delete these empry rows but for every situation a different data set is given included some empty x,y pairs.

The final result would be a table with some 100 or 200 x,y pairs

Does this work?

Formula:

Please Login or Register  to view this content.

Thanx for the suggestion.
It works however I prefer trend since it usages linear interpolation.
but trend cann't handle empty cells.

Yeah, this is a usefull solution. All the missing numbers are interpolated using linear regression and that’s what I wanted. Off course sometimes the estimated number isn’t perfect but this is very good for the moment.
It would be great if the whole dataset could be used for interpolating.
Thanx a lot!

witn regards Onno Kramer

ok, thanx
I try to help student with their project about purifying water.