Jun 14 2023 03:04 AM
Jun 14 2023 03:04 AM
I'm looking for some help/advice to consolidate/group granular data, my example is daily vehicle mileage over a months period, Column A would have vehicle registration numbers and then following columns would represent a day of the month and cells would populate with daily mileage total from vehicle tracking updated each day.
I'm trying to apply a formula or something that would enable me to group the % of vehicles that are in daily range brackets such as X% travel less than 100 miles per day X% travel less than 125 miles per day, X% travel less than 150 mile per day and so on, I'm currently manually doing this through conditional formatting but obviously some vehicles are in all groups so this takes time and I'm just wondering/hoping there is a better/easier way, any advise welcome, Thanks
Jun 14 2023 08:35 AM
Are you aware of the Pivot Table capability? From your description that would be where I'd go first.
If you are and haven't been able to make it work (or if you weren't but still need help) might I suggest you post a copy of the spreadsheet with all the data--the actual spreadsheet, not an image--on OneDrive or GoogleDrive with a link pasted here that grants access to it.
Jun 14 2023 11:30 AM
@mathetes Thanks, i use pivot tables quite a lot to summarise data but cannot see how this would work for what i would like it to do to group % bandings as i want it to calculate the groups, my spreadsheet layout is very simple as described above but will have 1000 rows down for vehicle column (as that's the fleet size) and the 28-31 additional columns across depending on the days count in the month all with a variable mileage total in each cell.
Jun 14 2023 11:58 AM
Jun 14 2023 05:06 PM
In the absence of actually having the data to work with--I don't think it should be necessary for me (or anybody else here who'd like to help) to create a data set--my suggestion would be to add a few helper columns or two to reflect those ranges.
But I will go back to my request that you post a copy of the workbook so that I and others can see what works rather than conceptualize in the abstract.
Jun 16 2023 02:16 AM