Determine the count over date range via Pivot/Macro
Dear Folks,
Excited to be here and learn from the Excel Guru's.
There is a specific task where I have been spending time over formulas and need your inputs if this can be done over pivot.
I have 100x Devices (Badge No or Serial No) which provide a certain performance reading daily. I need to know exactly which devices have been giving consistent reading in last 30 days, 29 days, 28 days and so on to identify each device accordingly.
Any support / direction is highly appreciated.
Last edited by daryldsouza83; 08-04-2023 at 04:37 PM. Reason: Title could be misleading
I'm not sure if this is what you want.
I notice that the range is 31 days and that they go from the current date on down.
Enter in the number of days on cell AM2.
The magic formula in column AJ is =COUNTA(OFFSET(E3,0,0,1,$AM$2))
The offset command means, Start in E3 and go down zero rows and right zero columns and give me a range 1 row deep and whatever the value in cell AM2 is columns wide.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Hey thanks dflak for your response.
It is 30 days, may be an error on me when creating this sample.
Thanks for formula, isn't exactly what I was looking for though I'll try to work with it for now.
Or this, not using Volatile OFFSET.Formula:
Please Login or Register to view this content.
What does this mean. . . isn't exactly what I was looking for though I'll try to work with it for now.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Hi TMS,
My apologies, I guess i am unable to explain here the actual request.
May be the below pic will help explain, What I am looking to do is find out over the last X days, which devices have been consistent.
Example - 24 Devices were provided data consistently over 31 days, and if I could view that list of meters from a click over Pivot table.
Capture.PNG
It seems to me that the meter in row 4 read consistantly for the last 21 days, 14-Jul through 4-Aug.
Based on that I added a column populate using: =IFERROR(E$2-INDEX(E$2:AI$2,AGGREGATE(15,6,(COLUMN(E3:AI3)-COLUMN(D3))/(E3:AI3=""),1)),31)
A pivot table is produced from the added column placing the count field in both the Rows and Values areas.
A pivot chart is produced from the pivot table.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thanks a lot JeteMC !!
This will definitely help me analyze over 600K devices.
Thanks for the rep.
You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks