-1

I have this data

Date        Data
8/25/2017   980
8/24/2017   64
8/23/2017   593   
8/22/2017   595
8/21/2017   
8/20/2017   
8/19/2017   794
8/18/2017   437
8/17/2017   
8/16/2017   
8/15/2017   
8/14/2017   629

What if i wanted (794-595)/3 in cell 21st August and 2*(794-595)/3 in cell 22nd August and similarly (629-437)/4 in 17th August, 2*(629-437)/4 in 16th August etc...

And there are only 2 missing sequences of data in the entire data set. But i want to be able to do all this without knowing the number of missing sequences in advance.

How to do this dynamically without regard to the number of missing values in between?

duplode
  • 33,731
  • 7
  • 79
  • 150
Abhay Saini
  • 97
  • 7
  • It's not clear how your formulas are being calculated. Why is it `2*` for the 22nd, and a new formula in August 17, but then in the 16th, that formula is `2*[formula]/4`? – BruceWayne Sep 13 '17 at 17:09
  • To do in column it will require vba and Stack Overflow is not a code for me site. Please show what you have tried. – Scott Craner Sep 13 '17 at 17:10
  • @BruceWayne The formula is for simple linear interpolation. For example, i have 5,_,_,10. Linear interpolation would require me to put 5, 5+1.66, 5+3.32, 10 as the series. – Abhay Saini Sep 13 '17 at 17:16
  • @Scott Craner. I don't know VBA. If you don't want to answer, kindly save your energy and avoid this question – Abhay Saini Sep 13 '17 at 17:16
  • Please see [How to Ask](https://stackoverflow.com/help/how-to-ask) - I'm still not clear on the formula you're using, or where the numbers come from. Is your data that you have in your example table a hard value, or the result of some formula? Also, I almost guarantee you @ScottCraner wants to answer your question. However, as mentioned, SO isn't a "code for me" site. We aren't (generally) likely to just provide answers without some effort or attempts on the OP's part. What have you tried so far? – BruceWayne Sep 13 '17 at 17:19
  • So the question remains, do you want a formula in a helper column or do you want vba to do it in column? If you want vba then you will need to add an attempt and add the tags so those who filter on those tags will see it. – Scott Craner Sep 13 '17 at 17:29
  • @BruceWayne The Numbers don't mean anything. They could be anything. The point is that there are certain missing values. Missing values need to be filled in. One of the ways to fill in data is just replace it with old values. But we don't want to go that route. I prefer to fill it with averages. For example, if there is only 1 missing value, i would take the mean of the previous and next value. If there are 2 missing values, i would want to linearly interpolate. Meaning i would want the difference between each missing value and the first/last values to be exactly the same. – Abhay Saini Sep 13 '17 at 17:31
  • @Scott Craner. I am okay with anything. With regards to Excel, i can easily do it with one formula for one instance. But sadly, i find it hard to do the dynamic thing with excel formulas. Maybe dynamic named ranges might help. But the next step would be to find the blank spaces in a column and am not sure how to do that using excel formulas. – Abhay Saini Sep 13 '17 at 17:34
  • Why the removal of correct answer? – Scott Craner Sep 20 '17 at 19:12
  • Am sorry what? I think i clicked on that tick again by mistake. So Sorry – Abhay Saini Sep 20 '17 at 20:00

1 Answers1

4

For a formula route you will need a helper column:

In row 2 of that helper column put:

=IF(B2="",INDEX(B:B,MATCH(1E+99,$B$1:B1))+((ROW() - MATCH(1E+99,$B$1:B1))*(INDEX(B2:INDEX(B:B,MATCH(1E+99,B:B)),MATCH(TRUE,INDEX((B2:INDEX(B:B,MATCH(1E+99,B:B))<>""),),0))-INDEX(B:B,MATCH(1E+99,$B$1:B1)))/(MATCH(TRUE,INDEX((B2:INDEX(B:B,MATCH(1E+99,B:B))<>""),),0)+ROW()-1-MATCH(1E+99,$B$1:B1))),B2)

And copy down the range

enter image description here

Then if you want you can copy and paste the values back over the original data.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • ...[What?](https://media.giphy.com/media/UvwI1X7XkbXq0/giphy.gif) Wow! I don't even know how you knew where to get started. (Perhaps I just need more coffee?) – BruceWayne Sep 13 '17 at 17:44
  • @BruceWayne it is a slight modification on a formula I use in one of my workbooks. I have encountered this problem before. – Scott Craner Sep 13 '17 at 17:46
  • Perfect answer! – Abhay Saini Sep 13 '17 at 17:53
  • Wow Scott! You are a genius ! – Arvinth Kumar Sep 13 '17 at 17:58
  • @ArvinthKumar did not realize this was a [duplicate](https://stackoverflow.com/questions/45889753/how-to-dynamically-do-a-linear-interpolation-of-data-in-a-row-with-missing-value). I would have closed it as such. – Scott Craner Sep 13 '17 at 18:01
  • Sometime duplicate questions are asked out of need. And people with no reputation cannot "get" people to view that question. – Abhay Saini Sep 13 '17 at 18:02
  • A slight modification might make the formula even better. The places first two cells, B1 and B2 will do better to have the $ removed from before the B. Makes it easier to drag the formula across and do it faster for a group of columns. – Abhay Saini Sep 13 '17 at 18:04
  • 1
    @AbhaySaini next time please link to the original question and state why your question is different, why the given answer does not work for you. Because had I seen the other question I would have closed this one. Just for future reference. – Scott Craner Sep 13 '17 at 18:04
  • @AbhaySaini your question only states the one column, but yes if you are dragging across then make the columns relative. – Scott Craner Sep 13 '17 at 18:05
  • What if i wanted to distinguish between the last extrapolations and the ones in the middle? For example, i have a blank as the last value. The blanks from that last value to the next filled value would be filled with the previous value. How to distinguish that "previous" value from normal values? – Abhay Saini Sep 13 '17 at 18:43
  • Now that is a new question with it's own problems. It adds a whole level of complexity. Ask a new question with the data. Link to this question as reference. Explain how excel is supposed to know that the last value is blank and not the next cell with a value. @AbhaySaini – Scott Craner Sep 13 '17 at 18:47