Excel Pivot tables - car type and average speed - counting data according to filters

Question

I have two columns of data: Car type and average speed (mph).

I am looking to arrange data in terms of car type and average speed. However, the average speed needs to the grouped into bins. For example, I need to count how many times estates cars only travel at speed of between 30 - 33 mph and how many times they travel at 33 - 36 mph.

Many thanks for you time!

Answer 1

It is possible to do the above using a pivot-table.

Highlight your data, select the Insert tab and then Pivot-Table.

Drag the Mean Speed field into the ‘COLUMNS’ area of the ‘PivotTable Fields’ toolbar

Drag the Car Type field into the ‘ROWS’ area of the Pivot Table Fields toolbar

Drag the Speed field into the ‘∑ VALUES’ area of the Pivot Table Fields toolbar also.

Use countif function in the pivot table toolbar (in the sum VALUES section of the pivot table toolbar right click the speed variable and then summarise data by count) to count the number of occasions the speed falls within each bin for each car type.

You should be done!