Forum Discussion
Expansion and interpolation of dated dated
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.
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.
11 Replies
You can practice vlookup here too if you like
https://gyankosh.net/msexcel/functions/lookup-and-reference/excel-functions-vlookup/
https://gyankosh.net/msexcel/practice/vlookup-examples/#gsc.tab=0
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 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.
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.
