Forum Discussion
Expansion and interpolation of dated dated
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.
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.
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
| Date | Productivity | Date Value |
| 1/2/2018 | 1.32 | 43102.00 |
| 1/3/2018 | 1.32 | 43103.00 |
| 1/4/2018 | 1.32 | 43104.00 |
| 1/5/2018 | 1.33 | 43105.00 |
| 1/8/2018 | 1.37 | 43108.00 |
| 1/9/2018 | 1.36 | 43109.00 |
After Expansion and Interpolation
| Date | Productivity | Date Value |
| 1/2/2018 | 1.32 | 43102 |
| 1/3/2018 | 1.32 | 43103 |
| 1/4/2018 | 1.32 | 43104 |
| 1/5/2018 | 1.33 | 43105 |
| 1/6/2018 | 1.34 | 43106 |
| 1/7/2018 | 1.35 | 43107 |
| 1/8/2018 | 1.36 | 43108 |
| 1/9/2018 | 1.34 | 43109 |
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,
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.
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.
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.