To perform an interpolation of the missing cells in the given matrix

D

dabohra

New Member
Joined
Oct 3, 2012
Messages
4
Hi

Request your help in developing an excel formula via which i can populate the empty portion of the provided excel model. Please take a look and let me know if it is possible.

Thanks,
Amit

Average Floor Area</SPAN>100</SPAN>125</SPAN>150</SPAN>175</SPAN>200</SPAN>250</SPAN>300</SPAN>350</SPAN>400</SPAN>450</SPAN>500</SPAN>600</SPAN>700</SPAN>800</SPAN>900</SPAN>1000</SPAN>1100</SPAN>1200</SPAN>1300</SPAN>1400</SPAN>1500</SPAN>1600</SPAN>1700</SPAN>1800</SPAN>1900</SPAN>2000</SPAN>2200</SPAN>2400</SPAN>2600</SPAN>3000</SPAN>
1,000</SPAN>1.252</SPAN>1.360</SPAN>1.468</SPAN>1.576</SPAN>1.684</SPAN>
1,500</SPAN>1.112</SPAN>1.182</SPAN>1.252</SPAN>1.323</SPAN>1.395</SPAN>
2,000</SPAN>1.095</SPAN>1.147</SPAN>1.199</SPAN>1.252</SPAN>1.360</SPAN>
2,500</SPAN>1.083</SPAN>1.125</SPAN>1.168</SPAN>1.252</SPAN>1.340</SPAN>1.430</SPAN>
3,000</SPAN>1.077</SPAN>1.112</SPAN>1.182</SPAN>1.252</SPAN>1.323</SPAN>1.395</SPAN>
4,000</SPAN>1.013</SPAN>1.040</SPAN>1.094</SPAN>1.147</SPAN>1.199</SPAN>1.252</SPAN>1.306</SPAN>
5,000</SPAN>0.996</SPAN>1.040</SPAN>1.083</SPAN>1.125</SPAN>1.168</SPAN>1.210</SPAN>1.252</SPAN>
6,000</SPAN>1.004</SPAN>1.040</SPAN>1.077</SPAN>1.112</SPAN>1.147</SPAN>1.182</SPAN>1.252</SPAN>
7,000</SPAN>1.008</SPAN>1.040</SPAN>1.071</SPAN>1.102</SPAN>1.132</SPAN>1.192</SPAN>1.252</SPAN>
8,000</SPAN>0.984</SPAN>1.013</SPAN>1.040</SPAN>1.068</SPAN>1.094</SPAN>1.147</SPAN>1.199</SPAN>1.252</SPAN>
10,000</SPAN>0.972</SPAN>0.996</SPAN>1.019</SPAN>1.040</SPAN>1.083</SPAN>1.125</SPAN>1.168</SPAN>1.210</SPAN>
12,000</SPAN>0.965</SPAN>0.984</SPAN>1.003</SPAN>1.040</SPAN>1.077</SPAN>1.112</SPAN>1.147</SPAN>1.182</SPAN>
14,000</SPAN>0.945</SPAN>0.961</SPAN>0.977</SPAN>1.008</SPAN>1.040</SPAN>1.071</SPAN>1.102</SPAN>1.132</SPAN>
16,000</SPAN>0.943</SPAN>0.957</SPAN>0.984</SPAN>1.013</SPAN>1.040</SPAN>1.068</SPAN>1.094</SPAN>
18,000</SPAN>0.929</SPAN>0.942</SPAN>0.967</SPAN>0.991</SPAN>1.016</SPAN>1.040</SPAN>1.065</SPAN>
20,000</SPAN>0.926</SPAN>0.949</SPAN>0.972</SPAN>0.996</SPAN>1.019</SPAN>1.040</SPAN>1.062</SPAN>1.083</SPAN>
25,000</SPAN>0.907</SPAN>0.924</SPAN>0.942</SPAN>0.959</SPAN>0.977</SPAN>0.996</SPAN>1.015</SPAN>1.032</SPAN>1.049</SPAN>1.066</SPAN>
30,000</SPAN>0.907</SPAN>0.921</SPAN>0.935</SPAN>0.949</SPAN>0.965</SPAN>0.980</SPAN>0.995</SPAN>1.010</SPAN>1.025</SPAN>1.040</SPAN>
35,000</SPAN>0.896</SPAN>0.907</SPAN>0.919</SPAN>0.932</SPAN>0.945</SPAN>0.957</SPAN>0.969</SPAN>0.982</SPAN>0.995</SPAN>1.008</SPAN>1.021</SPAN>
40,000</SPAN>0.899</SPAN>0.907</SPAN>0.916</SPAN>0.926</SPAN>0.937</SPAN>0.949</SPAN>0.961</SPAN>0.972</SPAN>0.984</SPAN>0.995</SPAN>1.007</SPAN>1.019</SPAN>
45,000</SPAN>0.898</SPAN>0.907</SPAN>0.916</SPAN>0.926</SPAN>0.935</SPAN>0.945</SPAN>0.955</SPAN>0.965</SPAN>0.975</SPAN>0.985</SPAN>0.995</SPAN>1.005</SPAN>1.015</SPAN>
50,000</SPAN>0.891</SPAN>0.898</SPAN>0.907</SPAN>0.916</SPAN>0.924</SPAN>0.933</SPAN>0.942</SPAN>0.950</SPAN>0.959</SPAN>0.968</SPAN>0.977</SPAN>0.986</SPAN>0.996</SPAN>1.015</SPAN>
60,000</SPAN>0.889</SPAN>0.895</SPAN>0.901</SPAN>0.907</SPAN>0.914</SPAN>0.921</SPAN>0.928</SPAN>0.935</SPAN>0.942</SPAN>0.949</SPAN>0.957</SPAN>0.965</SPAN>0.980</SPAN>0.995</SPAN>
70,000</SPAN>0.877</SPAN>0.884</SPAN>0.890</SPAN>0.896</SPAN>0.902</SPAN>0.907</SPAN>0.913</SPAN>0.919</SPAN>0.925</SPAN>0.932</SPAN>0.939</SPAN>0.945</SPAN>0.957</SPAN>0.969</SPAN>0.982</SPAN>
80,000</SPAN>0.869</SPAN>0.875</SPAN>0.881</SPAN>0.887</SPAN>0.893</SPAN>0.898</SPAN>0.903</SPAN>0.907</SPAN>0.911</SPAN>0.916</SPAN>0.921</SPAN>0.926</SPAN>0.937</SPAN>0.949</SPAN>0.961</SPAN>0.984</SPAN>
100,000</SPAN>0.863</SPAN>0.868</SPAN>0.872</SPAN>0.877</SPAN>0.882</SPAN>0.887</SPAN>0.891</SPAN>0.895</SPAN>0.899</SPAN>0.903</SPAN>0.907</SPAN>0.916</SPAN>0.924</SPAN>0.933</SPAN>0.950</SPAN>
120,000</SPAN>0.856</SPAN>0.859</SPAN>0.863</SPAN>0.867</SPAN>0.871</SPAN>0.875</SPAN>0.879</SPAN>0.883</SPAN>0.887</SPAN>0.891</SPAN>0.895</SPAN>0.901</SPAN>0.907</SPAN>0.914</SPAN>0.928</SPAN>
140,000</SPAN>0.851</SPAN>0.854</SPAN>0.857</SPAN>0.860</SPAN>0.863</SPAN>0.867</SPAN>0.871</SPAN>0.874</SPAN>0.877</SPAN>0.880</SPAN>0.884</SPAN>0.890</SPAN>0.896</SPAN>0.902</SPAN>0.913</SPAN>
160,000</SPAN>0.850</SPAN>0.853</SPAN>0.855</SPAN>0.858</SPAN>0.860</SPAN>0.863</SPAN>0.866</SPAN>0.869</SPAN>0.872</SPAN>0.875</SPAN>0.881</SPAN>0.887</SPAN>0.893</SPAN>0.903</SPAN>
180,000</SPAN>0.846</SPAN>0.849</SPAN>0.851</SPAN>0.854</SPAN>0.856</SPAN>0.858</SPAN>0.860</SPAN>0.863</SPAN>0.866</SPAN>0.869</SPAN>0.874</SPAN>0.879</SPAN>0.884</SPAN>0.895</SPAN>
200,000</SPAN>0.846</SPAN>0.848</SPAN>0.850</SPAN>0.853</SPAN>0.855</SPAN>0.857</SPAN>0.859</SPAN>0.861</SPAN>0.863</SPAN>0.868</SPAN>0.873</SPAN>0.877</SPAN>0.887</SPAN>
225,000</SPAN>0.845</SPAN>0.847</SPAN>0.849</SPAN>0.851</SPAN>0.853</SPAN>0.855</SPAN>0.856</SPAN>0.858</SPAN>0.862</SPAN>0.867</SPAN>0.871</SPAN>0.879</SPAN>
250,000</SPAN>0.842</SPAN>0.844</SPAN>0.846</SPAN>0.848</SPAN>0.849</SPAN>0.851</SPAN>0.853</SPAN>0.855</SPAN>0.858</SPAN>0.862</SPAN>0.866</SPAN>0.873</SPAN>
275,000</SPAN>0.839</SPAN>0.841</SPAN>0.843</SPAN>0.845</SPAN>0.847</SPAN>0.848</SPAN>0.850</SPAN>0.852</SPAN>0.855</SPAN>0.858</SPAN>0.862</SPAN>0.868</SPAN>
300,000</SPAN>0.839</SPAN>0.841</SPAN>0.842</SPAN>0.844</SPAN>0.846</SPAN>0.847</SPAN>0.849</SPAN>0.852</SPAN>0.855</SPAN>0.857</SPAN>0.863</SPAN>
350,000</SPAN> 0.835</SPAN>0.836</SPAN>0.839</SPAN>0.840</SPAN>0.841</SPAN>0.843</SPAN>0.845</SPAN>0.847</SPAN>0.850</SPAN>0.853</SPAN>0.857</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL span=4><COL><COL><COL><COL span=2><COL><COL span=4></COLGROUP>
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
There may be a couple of ways to try to do this, the value in any particuar cell might be calculated from the values in the leftmost column in the same row and the uppermost row in the same column, or perhaps the trend can be looked at from all the other values in the same row (or is it column? (I'd need an answer to this one)).
So if you could give a clue as to what these figures represent and how they might be calculated…
 
Upvote 0
I would like to calculate or forecast for cell where row value is 2000 and column value is 100 and it considers the value of 1.252 and 1.112. This is one way to look at it. Other way is to look at row value 1000 and all the value beside that and also take the value header from 100 - 200 to get a value for cell where it meets 250 & 1000. Let me know if this helps.
 
Upvote 0
dabohra said:
I would like to calculate or forecast for cell where row value is 2000 and column value is 100 and it considers the value of 1.252 and 1.112.
Click to expand...
Two points isn't much to go on, but:
*AB
1Average Floor Area100
21,0001.252
31,5001.112
42,0000.972

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B4=TREND($B$2:$B$3,$A$2:$A$3,A4,TRUE)

<tbody>
</tbody>

<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4

This is a linear extrapolation which isn't likely to be very reliable since if we look at other vertical series we see a curve rather than a straight line. That's why I'd like some clue as to what these figures represent and how they might be calculated.
dabohra said:
Other way is to look at row value 1000 and all the value beside that and also take the value header from 100 - 200 to get a value for cell where it meets 250 & 1000.
Click to expand...
With this:
*ABCDEFG
1Average Floor Area100125150175200250
21,0001.2521.361.4681.5761.6841.9

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
G2=TREND($B$2:$F$2,$B$1:$F$1,G1,TRUE)

<tbody>
</tbody>

<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4

again a linear trend (best straight line) is used. This time the value will be a little more reliable as (a) the existing values are almost in a straight line and (b) there are more than two points, however, the relation is not likely to be linear as examination of row series further down the table reveals.

Using the same technique to get the value for 2000/100 yields:
*ABCDEFG
42,0001.0411.0951.1471.1991.2521.36

<colgroup><col style="width:30px; "><col style="width:130px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
B4=TREND($C$4:$G$4,$C$1:$G$1,B1,TRUE)

<tbody>
</tbody>

<tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4

instead of 0.972

dabohra said:
Let me know if this helps.
Click to expand...
Not a lot. Again, if you could give a clue as to what these figures represent and how they might be calculated then…
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
excel function to adopt
Replies
0
Views
513
lovella
L

Forum statistics

Threads
1,215,335
Messages
6,124,327
Members
449,155
Latest member
ravioli44

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top