SOLVED

Expansion and interpolation of dated dated

Copper Contributor

I have dated productivity data that I need to evaluate after the dated data columns are expanded to include all dated data, inclusively, followed by interpolation of productivity data for off working days.  I need guidance/help in automating that process.  Please see the attached Excel sheet.  Automation with macros and VBA is currently beyond my capabilities.  I am error prone doing it manually.  My hope is that there is a template or simple suggestion that I might apply.  Thanks for whatever help you may be willing to offer.

11 Replies

@Happy-Hoppy 

 

There was an attached word document, but not an Excel sheet. When you do upload the sheet itself, I hope it will be clear what you're asking. Your opening description makes it clear that you understand what you're to do, but it's still pretty abstract (i,e., vague) to those of us who are less closely involved.

Are you asking for

  • help in evaluating the productivity data
  • or help in expanding the dated columns to include. (etc)...
  • or help in interpolation of productivity data for off working days
  • or all of the above?

In any case, I hope those questions make clear what needs to be clarified.

 

@mathetes 

Thanks much for your reply and identification of my attachment error.  I wonder what word document I included.  Hopefully, I'm attaching the correct Excel sheet this time.

 

Regarding your questions, yes I do need help in automating a process to:

 

  • expand/lengthen the dated columns to include missing dates/data-values and
  • perform, and include, simple linear interpolations of Productivity data for the off-working days.

E.g.,                 Before

DateProductivityDate Value
1/2/20181.3243102.00
1/3/20181.3243103.00
1/4/20181.3243104.00
1/5/20181.3343105.00
1/8/20181.3743108.00
1/9/20181.3643109.00

After Expansion and Interpolation

DateProductivityDate Value
1/2/20181.3243102
1/3/20181.3243103
1/4/20181.3243104
1/5/20181.3343105
1/6/20181.3443106
1/7/20181.3543107
1/8/20181.3643108
1/9/20181.3443109

 

I have various models to that I wish to use to evaluate the productivity data.

Thanks for whatever help you may be able to offer.  It is great that there are folks like you willing to help.

 

Best regards,

 

 

@mathetes 

 

Thanks for the information.  I will need to study the information to try to understand.  It seems that most of the explanations/examples are a bit cryptic for my immediate understanding.  I have no doubt the solution to my problem is there and I appreciate your sorting through history to help me.

@Happy-Hoppy 

Thanks much for your reply and identification of my attachment error.  I wonder what word document I included. 

 

You can go back and download the first attachment yourself from your first posting. All it was, though, was the exact same text that you had posted. So maybe you had drafted what you wanted to say in Word and then accidentally just posted that Word doc.

 

Hopefully, I'm attaching the correct Excel sheet this time.  You did

 

Regarding your questions, yes I do need help in automating a process to:

 

  • expand/lengthen the dated columns to include missing dates/data-values and
  • perform, and include, simple linear interpolations of Productivity data for the off-working days.

I'm afraid I'm not able to help here--partly because it's still not clear how this fits into a larger context (where are those missing data elements coming from, for example) and whether or not it can be automated at all going forward.

 

Unless someone else from among the various Excel experts here in the techcommunity can help, you might be better served by finding somebody in your workplace who knows Excel AND your actual work requirements.

@mathetes 

I did benefit from the VLOOKUP function example.  However, my explanation about interpolating productivities between nonwork dates was not clear.  Because I don’t have any staff who is Excel-savvy I am offering another explanation to hopefully better explain what I need (see screen shot of spread sheet below).

 

I need a formula?, function?, macro? for column “F” entries to interpolate productivities for the nonwork dates that are indicated in column “E” with “0.000”.  Sometimes there are nonwork date spans from 1 to 14 (or more) days, not just 2 or 3 days.  My example database has a span from Tuesday, January 2, 2018 thru Friday, January 31, 2020 with about 236 nonwork dates requiring productivity interpolations.  My entries for column “F” were done by copying formulas for specific nonwork date spans – cumbersome and error prone.

 

Your help would be very much appreciated.  My spread sheet and this request are attached.

 

Happy-Hoppy_0-1586451580479.png

 

 

@Happy-Hoppy 

 

That's a very tricky formula you're looking for. I'm not at all sure that I'm the right person to come up with the most elegant answer. And I know that I'll have to ponder it, play around with it.

 

Because of the variable length of numbers of days for which an intepolation formula is going to have to be developed, we can't just go with "Find a blank row and interpolate between the prior row and the third (or fourth) row down"    Instead, it's

  • Find a blank row
  • Note blank row minus 1 [StartRow]
  • Find the next non-blank row [EndRow]
  • Determine the number, then, of blank rows {EndRow-StartRow-1]=[N]
  • Craft a formula that goes progressively from 1 to N interpolating between the values of StartRow and EndRow

 

If I were to come up with it, I'd be looking into INDEX, MATCH, ROW, INDIRECT...just for starters. But I'll bet there are folks here among techcommunity experts that could do it much more directly.

 

@mathetes 

Thanks for responding.  I'll study the INDEX, MATCH, ROW, INDIRECT functions and try to work it out.  I'm not confident I will.  I've struggled for a couple of days and have not progressed.  After I study the functions and try to apply them, and fail, how can I re-post my need to the techcommunity?  Is it possible they are monitoring my plight or do I need to take initiate another request for help?  As you surely can tell, I'm totally ignorant of seeking help.

 

Thanks again.

@Happy-Hoppy 

 

I woke up this morning thinking about Excel's statistical functions, wondering if some kind of regression analysis would serve your purposes. I've not tried it, maybe only used this kind of thing once in my life, many years ago, but the TREND function might serve your purpose. Here's a link that describes it. It can't hurt to try it.

https://www.excelfunctions.net/excel-trend-function.html

From what I read, you probably would just leave out the optional "new x's" because they're the dates for which the value is zero. What you'd probably want to do, though, is turn those cells into blanks (totally empty cells)...

 

The reason I thought of it is that, though the formulas you've been developing are very "precise" they really are attempts to just fill in the blank with what might have been [by the way, you've never made totally clear why it's necessary to do that]....and though TREND might produce different values than your current interpolation formulas, it would still be doing so trying to "normalize" the trend line.....so would seem to be every bit as legitimate a way to do that. Assuming that it does indeed fill in those missing data points.

 

Take a look at that (and the other statistical functions, as needed). I've never formally studied statistics, so may be steering you astray, but it does seem to me that this may be more along the line of what you need than any other more detailed, but maybe no more accurate, form of calculation.

@Happy-Hoppy 

 

So I was able to get what appear to be reasonable results using TREND, but it did require moving some of your data around. And I only did it for maybe 30 days worth of your sample spreadsheet.

 

It would be possible to bring all this back together into a single database....but you need to take a look and see if this is even acceptable data.

best response confirmed by Happy-Hoppy (Copper Contributor)
Solution

@Happy-Hoppy 

 

Nope -- I managed to take all your blank dates and develop numbers using TREND...but the results are pretty clearly not what you want. I do think they're an accurate TREND line, but because your overall trend in the productivity numbers is upward over the two years in questions, that general upward trend is what the TREND produced numbers show....increasing by approximately 1/1000th with each new entry. That is NOT an interpolation, however, between the last worked day and the next worked day.

 

It might be possible to use TREND on a monthly basis, say , for work days missed during any given month....but with over two years of data, that general trend is too general to be meaningful in the granularity of specific weekends.

 

was an interesting exercise, but didn't produce the outcome you wanted.

 

Let me add one new function to that series I gave you earlier as functions you might want to study and try: OFFSET.

 

And you asked about coming back and posting. First, it's entirely possible that somebody far more expert than I might pick up this thread. But if not, after you've continued a few other things on your own, you might come back and start a new thread. It wouldn't be bad to mention a link to this thread, just to keep people from going down blind alleys.

1 best response

Accepted Solutions
best response confirmed by Happy-Hoppy (Copper Contributor)
Solution

@Happy-Hoppy 

 

Nope -- I managed to take all your blank dates and develop numbers using TREND...but the results are pretty clearly not what you want. I do think they're an accurate TREND line, but because your overall trend in the productivity numbers is upward over the two years in questions, that general upward trend is what the TREND produced numbers show....increasing by approximately 1/1000th with each new entry. That is NOT an interpolation, however, between the last worked day and the next worked day.

 

It might be possible to use TREND on a monthly basis, say , for work days missed during any given month....but with over two years of data, that general trend is too general to be meaningful in the granularity of specific weekends.

 

was an interesting exercise, but didn't produce the outcome you wanted.

 

Let me add one new function to that series I gave you earlier as functions you might want to study and try: OFFSET.

 

And you asked about coming back and posting. First, it's entirely possible that somebody far more expert than I might pick up this thread. But if not, after you've continued a few other things on your own, you might come back and start a new thread. It wouldn't be bad to mention a link to this thread, just to keep people from going down blind alleys.

View solution in original post