# Filling down a formula in Excel to a sparse range

I am creating an excel sheet out from the sounding table book of a fuel storage tank where the volume values were given for every fifth centimeter. I have to populate the empty cells with the interpolation values to use VLookUp function afterwards in order to get volume as a function of sounding (first column) and trim (x axis). I managed to insert four empty lines between each given values. Then I fill the cells in between by interpolating the values of upper and lower rows. Then I copy the interpolation range and paste it to the empty range. This is repeated hundreds of times. So I wonder if there could be an easier way as my total number of tanks is about 20 and the depth (y axis) is about 10000 cm. The problem is that if I copy and paste only a formula the empty destination cell is still overwritten to empty value. Otherwise I could reuse the formulas from the first tank.

``````...
...
125     475.4   474.9   474.3   473.7   473.1   472.4   471.8   471.2 ...
126     475.4   474.9   474.3   473.7   473.1   472.4   471.8   471.2 ...
127     474.7   474.1   473.5   472.9   472.3   471.6   471.0   470.4 ...
128     473.9   473.3   472.8   472.1   471.5   470.9   470.3   469.6 ...
129     473.2   472.6   472.0   471.3   470.7   470.1   469.5   468.8 ...
130     472.4   471.8   471.2   470.6   469.9   469.3   468.7   468.0 ...
131
132
133
134
135     469.3   468.7   468.0   467.4   466.8   466.2   465.5   464.9 ...
136
137
138
139
140     466.1   465.5   464.9   464.3   463.6   463.0   462.4   461.8 ...
141
142
143
144
145     463.0   462.4   461.8   461.2   460.5   459.9   459.3   458.6 ...
146
147
148
149
...
``````

• It's not clear what you are trying to do. Can you edit your question to explain in more detail. Commented Nov 14, 2017 at 14:07
• Amended some details Commented Nov 14, 2017 at 15:00
• And the best answer goes to..... github.com/DanGolding/… It's just a ready formula for my task. You feed the table into the formula and you get the interpolated value. Unfortunately it's impossible to contact the author on GitHub to express my gratitude for his great job.. Commented Apr 18, 2019 at 15:34
• I believe that this is still unclear. (1) Your sample data appear to be inconsistent with your description of the problem. (2) You accepted an answer (here on Super User) that appears not to address the full scope of your question. (3) The answer that you found elsewhere and linked to appears to be answering a different question — so what, exactly, are you asking? Commented Apr 18, 2019 at 19:09

Based on what best I understand your question, I suggest a solution primarily based on OFFSET function and two Helper Columns. This is done for a single column. You shall need to repeat the same for all the columns.

This solution involves recreating the entire column elsewhere thru OFFSET Formula and then you need to Copy-Paste Special --> Values back to original column.

Assuming your data is in Cells B1:B16. Now create two helper columns. A column to the left with repeating sequence 0 1 2 3 4. All you need to do is select this range and simply paste it across all the applicable rows below. The sequence shall repeat again in the pasted cells. Similarly a column to the right with repeating sequence 0 4 3 2 1.

Now in D1 put the following formula and drag it down up to the intended length of the column.

``````=IF(ISNUMBER(B1),B1,OFFSET(B1,-VALUE(A1),0)-(((OFFSET(B1,-VALUE(A1),0)-OFFSET(B1,VALUE(C1),0))/5)*A1))
``````

Now simply Copy-Paste --> Paste Special --> Values, Column D to elsewhere and rebuild your entire sheet in this fashion.

This way you do not need to manually copy the formula adjusting the end value cells again and again between the two end values.

Assumptions -

1. There are exact 4 rows gaps between all applicable cells.

2. The extrapolation is not based on any complex maths, a simple subtraction of equal volumes between the two end values. If you are using any complex maths, possibly you may need another solution.

3. Values are always in decreasing order from top to down.

Explore this solution and see if it suits you.

• Hello, you got me absolutely right with all the assumptions. Thank you very much for your time, nothing to add here. Except that it looks that you are still dragging formulas instead of making an easy double click in the lower right cell corner to propagate it for the whole required range down. It will save a lot of effort. Commented Nov 16, 2017 at 11:01
• Yes absolutely, in my case I had just sample of rows to drag down. You should certainly use Double Click method for so many rows to handle. Commented Nov 16, 2017 at 11:05
• Also, why are you using Value(A1), Value(C1) etc. I put it straight "=IF(ISNUMBER(H6),H6,OFFSET(H6,-\$O6,0)+\$O6*(OFFSET(H6,\$P6,0)-OFFSET(H6,-\$O6,0))/5)" Commented Nov 16, 2017 at 11:12
• Just to be on safer side in a very rare case if the reference number appears as Text and somehow it fails, but it does not seem so though. Commented Nov 16, 2017 at 12:05