Bulk summary of granular data

[Ryansmills1840]

Hi All, 

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  

[#2]

@Ryansmills1840 

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.

[#3]

@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.

[#4]

what i should add is that i need it to only include in the % groups where the vehicle has consistently throughout each day in the month been in the lowest groups i.e. a van doing 90 miles each day for 30 days but then doing 120 miles on the 31st day (or any order) wouldn't be in the <100 group it would move to the <125 & < 150 grouping, hope that makes sense

[#5]

@Ryansmills1840 

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.

[#6]

Thanks, i did manage to get there using a pivot table in the end, i was just requiring a COUNTIF formula to configure the dally mileages into a group that i could pivot the whole sheet on to provide summary & percentage etc.

[#7]

Good to hear. It's always better learning when you figure it out for yourself, so great going!