Log In
Register
Home
Excel Expert
December 1, 2020
Offline
Hello all,
My first post, hopefully, I can explain myself clearly as I am a novice in using excel.
I am trying to find out how to set a pivot table/create formula, whichever way is easier, to calculate value difference prior the specific time and to calculate total time within time range where no activity has been determined.
I have added excel file which hopefully explains it, so I am looking for 4 value calculations, explained on the right side. I have done the first figures for the 01/10/20, and highlighted them in yellow as well on the data rows, just to show where I found the data. Obviously, I need to learn how to do this more efficient, rather than looking for the calculations manually.
Any help will be highly appreciated.
Many Thanks
Kaspars
July 16, 2010
Offline
Hi Kaspars,
Welcome to our forum! Please try uploading your file again. You need to click the 'Start Upload' button after selecting your file.
Thanks,
Mynda
Excel Expert
December 1, 2020
Offline
Sorry, the initial file was exceeding the limit.
July 16, 2010
Offline
Hi Kaspars,
Do you have Microsoft 365/Office 365? I need to know what version of Excel you have so I know what functions you have available.
Also, do you want these results for each date in the dataset?
Mynda
Excel Expert
December 1, 2020
Offline
Yes, it would be 365. Yes- please it would be for each date. If it makes things easier the time could be formatted in the numeric value.
Thank you 🙂
July 16, 2010
Offline
Hi Kaspars,
Thanks for clarifying. Your first example, "Minutes past target confirmation time of 14:10" has 2:38 PM as the earliest confirmation time after 2:10 PM, however on row 6631, 6632 and 6633 there is a time of 2:34:39 PM.
Did you simply overlook this earlier time, or is there something else I need to take into account, as the data isn't sorted.
Mynda
Excel Expert
December 1, 2020
Offline
Sorry Mynda, my mistake. Data wasn`t sorted correctly. I have changed it now. It should be sorted as basic as the earliest data and earliest time.
July 16, 2010
Offline
Hi Kaspars,
Thanks for clarifying. Please see file attached which uses the new dynamic array functions:
Kind regards,
Mynda
Excel Expert
December 1, 2020
Offline
Thank you Mynda, that is excellent!!! And it is another reminder that I need to learn a lot about Excel.
If it is possible, could you please adjust first time slot as - 1st Gap after 14:00, instead of 14:10. If it is time-consuming please leave it 🙂 I tried to do it today, but no luck- only more questions in my mind 😀
Also, I have attempted (again unsuccessfully) to add more data to the sheet which consists of your formulas, as I have another 104 users to add and calculate. Thought that copying/pasting additional data and dragging down the formula would work- but it didn't 🙁 Which is again my lack of knowledge so far.
July 16, 2010
Offline
Hi Kaspars,
You can change the formula in cell H3 to this:
=XLOOKUP(G3#+0.583333333333333,Table1[Date Time],Table1[Confirmation Time],"On Time",1)-0.583333333333333
0.583333333333333 is the time serial number for 14:00. If you enter 14:00 in a cell and then format it as general, you'll get 0.583333333333333.
More on working with dates and time in Excel here.
The source data in columns A:E is formatted in an Excel Table. All you need to do is paste your new data on the very next blank row at the bottom of the table and the ranges will expand to incorporate the new data in the Table's range. This will in turn automatically feed through to the formulas. As more dates are added, you'll need to copy the formulas in columns I:K down.
Note: the formulas don't differentiate between different users' data. If you want separate results for the different users then let me know and provide sample data containing multiple users.
Mynda
Excel Expert
December 1, 2020
Offline
Thank you, another set of new information for me to learn 🙂 I have attempted numerous times to adjust the formula, but no success. I believe my MO Excel doesn't support XLOOKUP 🙁 I might be wrong, but I have that assumption. I will add screenshots.
*Understand now the point about pasting new data and adjusting formula range.
*If we add more data with multiple users, would I be able to select a filter on - Confirmed by and then see the calculation?
Preferably I would calculate these measures every 3 months, so trying to create it dynamically, so I can add the data, do the calculation and after 3 months remove existing data and update with a new one.
July 16, 2010
Offline
Hi Kaspars,
You need to change your update channel from semi-annual to monthly to get the XLOOKUP function. Speak to your IT people to make that happen.
If you want to filter the data by the user, you'd need to modify the formulas to include a filter for the selected user ID. See attached, where you can choose the user from the drop down list in cell G2. There's only one user in the list at the moment, but when you update the data, it will automatically include new user IDs.
Mynda
Excel Expert
December 1, 2020
Offline
Thank you, Mynda, much appreciated 🙂
Excel Expert
December 1, 2020
Offline
HI Mynda, sorry to ask again, but I still can`t get the results. I must be doing something wrong.. I will add the recent file which you have created and new data for the user2.
Steps that I am taking:
1. Copying columns A & B from file user2 data
2. Pasting them into columns A & B within file kaspars_time_calcs2
3. It updates the formulas successfully in Columns C & D
4. Copying column C from file user2 data
5. Pasting them into column C within file kaspars_time_calcs2
6. This adds another user in the filter G2
So far so good 🙂
The problem is that it doesn't update the data into G: K Columns
What am I missing, please?
Many Thanks
Kaspars
July 16, 2010
Offline
Hi Kaspars,
It works fine for me. Did you get your version of Excel updated to include the dynamic array functions?
Mynda
Excel Expert
December 1, 2020
Offline
I wasn`t able to update my works PC for up to date Excel version. But I have been using Excel subscription on my personal PC through my UNI`s account. At least it did recognize XLOOKUP functions. But it looks like it still missing something. I have added a screenshot with the current Excel version which I am using. It is strange that it seems like it does update only one cell H20, when I filter for USER2. When you update the data, are you then selecting USER2 through G2 filter and it updates all the times on the right side?
Hmm.. It seems like I might need to purchase a current, up to date Excel version? If that is the case can you please advise which version I should go ahead with?
Many Thanks
Kaspars
July 16, 2010
Offline
Hi Kaspars,
That version you have on your Uni PC should work. Please send me the file you're using that you've added the user 2 data to so I can test on my PC.
Mynda
Excel Expert
December 1, 2020
Offline
Hi Mynda,
Added as requested- 1.Initial Template with user1, 2. Data from 2nd user 3. My outcome when I add data from user 2 to Template 1
Many Thanks
Kaspars
July 16, 2010
Offline
Hi Kaspars,
It appears to work fine for me. However, there's only one value that's different and that's on October 26 for the Minutes past target confirmation time of 14:00.
Is it that you're expecting different values for the different users and that's why you don't think it's working?
Mynda
Excel Expert
December 1, 2020
Offline
Hi Mynda, yes you are correct- I was expecting the values to change. I did start everything from the beginning and added another users data and it worked correctly! 🙂 All the values updated and user filters worked as well. Sorry for taking this long, and thank you for all the help and patience 🙂
1 Guest(s)
