Excel tables or formulas for data analysis

Copper Contributor

I have a data set of all our inbound calls from last week and what times the calls came in. To be able to monitor when our peak periods were, I want to create a pivot or table that tells me how many calls we got last week in each 15minute time slot. for example, this is part of our data set;

loljay_0-1650346573967.png

 

and I want to be able to put the data in here (below) for how many calls last week came in between 7.30 - 7.45 for example.

loljay_1-1650346127991.png

 

Is there a formula I can use to pull this data into the cells? or can someone help to create a pivot or normal table?

 

Thanks!

 

4 Replies

@loljay 

 

With Power Query

_Screenshot.png

 

Sample attached

@L z. 

hey thanks 

its just not letting me see any formulas in that sample sheet... would you be able to send the formula you used in column D of the sample please?

 

Tysm

@loljay 

There's no Excel formula as this is done with Power Query

@loljay 
Here are couple of more variants

- PivotTable (if you work with data model)

- formula (if you work with dynamic arrays, i.e. Excel 365 or 2021)