CODEWITHSUNDEEP
excelexcel-formula
Hephaestus
Hephaestus

Reputation: 59

Filter column for substring and return max of adjacent cells -- Excel 2013 (no macros)

I would like to obtain the max call time that was on each day, the average of all the calls that day, and any calls that exceeded 20 seconds.

Due to the security policies at work I am limited to the formulas provided by excel, I can't use macros or VB (otherwise I would just write something suitable). I don't have much experience with excel, so I apologize if this is a simple one. I have tried to find ways to search and return a subarray that I could easily manipulate, but it seems the only way to do that is with macros. Currently I think I can use VLOOKUP, but I don't really understand enough about it know if I can use this with another function.

Essentially, if there is a formula or a combination of formulas that I can use that will allow me to collect all data from column B that shares the same values in column A, I could nest that in the formulas I need to calculate the data.

Any help is appreciated!

Sample data

Upvotes: 0

Views: 126

Answers (1)

teylyn
teylyn

Reputation: 35900

This can be done with a pivot table in just a few clicks.

First, turn the source data into an Excel Table object, by selecting one cell in the table and then using Insert > Table or Ctrl-T. Add a helper column for the calls over 20 seconds. Let the column be "long calls" and the formula

=IF([@Duration]>TIME(0,0,20),1,0)

Then, with a cell of the Excel Table selected, click Insert > Pivot Table. (The pivot table is now linked to the Excel Table object and when more data is added you can refresh the pivot table without having to change the definition of the source data.)

Drag the Day field into the rows area and the Duration field into the Values area. Right-click any of the value results in the pivot table, and in the Value Field Settings dialog select Max. Then click on Number Format button and set the custom number format of hh:mm:ss. Confirm all dialogs.

If you want to format the date, right click any of the visible dates in the Pivot table, select Field Settings, click Number Format and then set the custom format ddd.

Drag the duration field into the values area again and set its calculation method to Average.

Drag the "long calls" field into the values area.

Hide the Grand Totals via the Pivot Table tools Design ribbon command.

Now you can add more data to the table in columns A to B, right click the pivot table and select refresh.

enter image description here

Upvotes: 2

Related Questions