0

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  
...  

Thanks for your attention.

4
  • 1
    It's not clear what you are trying to do. Can you edit your question to explain in more detail.
    – Blackwood
    Commented Nov 14, 2017 at 14:07
  • Amended some details
    – off-signer
    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..
    – off-signer
    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

1 Answer 1

3

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.

enter image description here

4
  • 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.
    – off-signer
    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.
    – patkim
    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)"
    – off-signer
    Commented Nov 16, 2017 at 11:12
  • 1
    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.
    – patkim
    Commented Nov 16, 2017 at 12:05

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .