Help on a formula to post in the cell the lastest result of Average Call Per Hour

mr_ITtoyou

New Member
Joined
Mar 26, 2021
Messages
31
Office Version
  1. 2016
Platform
  1. Windows
I have a spreadsheet for call center agents' call stats with 12 months. I have a cell, far right, column DF, (Average Call Per Hour). Below the spreadsheet, I have a formula that calculates the Average calls per hour, Year To Date, for each month, Row 35-41. My question is, is there a formula that would place the latest result from each month, Row 35-41 to be placed in the cell DF 4-10. To clarify, (I hope), after entering the data in Jan. the calculated resaults will be in Column F-35-41 and then those results will be placed in the Average Call Per Hour, DF 4-10. Then after the data is entered the results for Feb. will be placed in Average Call Per Hour, DF 4-10, override Jan. results, and so on for each month. Is there a formula for DF 4-10, if Feb P 35-41 is greater than Jan. F 35-41 then enter Feb. results?
The file XI2bb would keep freezing when I pressed Mini Sheet Capture, so I placed a link with the Excel spreadsheet. Thanks in advance for any help on this.

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows
The formula is correct, but that one will only return the last value from the Average Calls handled column, rather than the average of all those columns
Agent Calls Spreadsheet sample.xlsx
CFGLOPUXYADAGAHAMAPAQAVAYAZBEBHBIBNBQBRBWBZCACFCICJCOCRCSDFDG
3Total Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourLast averageAveraged Amount Of Calls Per Hour in total
4445716.268512727.111445716.268512727.111445716.268512727.111    445716.268512727.111390616.3936.396.6635071
58201097.523629956.6218201097.523629956.6218201097.523629956.621    8201097.523629956.621522766.8686.877.0829596
6707868.221661798.367707868.221661798.367707868.221661798.367    707868.221661798.367649689.5449.548.407967
7552628.9035915710.368552628.9035915710.368552628.9035915710.368    552628.9035915710.3685545310.45310.459.6899811
8463686.809586738.027463686.809586738.027463686.809586738.027    463686.809586738.027501559.1099.117.5880452
Sheet1
Cell Formulas
RangeFormula
F4:F8,CR4:CR8,CI4:CI8,BZ4:BZ8,BQ4:BQ8,BH4:BH8,AY4:AY8,AP4:AP8,AG4:AG8,X4:X8,O4:O8F4=D4-E4
G4:G8,CS4:CS8,CJ4:CJ8,CA4:CA8,BR4:BR8,BI4:BI8,AZ4:AZ8,AQ4:AQ8,AH4:AH8,Y4:Y8,P4:P8G4=IFERROR(C4/F4,0)
DF4:DF8DF4=INDEX(B4:DE4,AGGREGATE(14,6,(COLUMN($B$3:$DE$3)-COLUMN($B$3)+1)/($B$3:$DE$3=$G$3)/(B4:DE4>0),1))
DG4:DG8DG4=SUMIFS(B4:DE4,$B$3:$DE$3,$C$3)/SUMIFS(B4:DE4,$B$3:$DE$3,$F$3)
 
Solution

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

mr_ITtoyou

New Member
Joined
Mar 26, 2021
Messages
31
Office Version
  1. 2016
Platform
  1. Windows
So it looks like that the formulas, =INDEX(B4:DE4,AGGREGATE(14,6,(COLUMN($B$3:$DE$3)-COLUMN($B$3)+1)/($B$3:$DE$3=$G$3)/(B4:DE4>0),1)) are collecting data from all of the cells across, row 4 I just need the data from $G4,$P4,$AH4,$AQ4,$AZ4,$BI4,$BR4,$CA4,$CJ4,$CS4,$DB4,$G4,$P4,$AH4,$AQ4,$AZ4,$BI4,$BR4,$CA4,$CJ4,$CS4,$DB4, the columns of the Averaged Amount Of Calls Per Hour with the result in column $DF4, but I would need to add (G4+P4) /2 for Feb. to get Averaged Amount Of Calls Per Month and the result show up in DF4. Then when the data is entered in March, I would need to add (G4+P4+Y4) /3 for March. to get Averaged Amount Of Calls Per Month and the result show up in DF4, so Feb. results would be replaced with March results, Averaged amount of calls per hours for 3 months. If that is what the formula is doing, sorry that I am having a hard time seeing that. or understanding it.
 

mr_ITtoyou

New Member
Joined
Mar 26, 2021
Messages
31
Office Version
  1. 2016
Platform
  1. Windows
The formula is correct, but that one will only return the last value from the Average Calls handled column, rather than the average of all those columns
Agent Calls Spreadsheet sample.xlsx
CFGLOPUXYADAGAHAMAPAQAVAYAZBEBHBIBNBQBRBWBZCACFCICJCOCRCSDFDG
3Total Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourTotal Calls HandledMonth Total Hours Login to QueueAveraged Amount Of Calls Per HourLast averageAveraged Amount Of Calls Per Hour in total
4445716.268512727.111445716.268512727.111445716.268512727.111    445716.268512727.111390616.3936.396.6635071
58201097.523629956.6218201097.523629956.6218201097.523629956.621    8201097.523629956.621522766.8686.877.0829596
6707868.221661798.367707868.221661798.367707868.221661798.367    707868.221661798.367649689.5449.548.407967
7552628.9035915710.368552628.9035915710.368552628.9035915710.368    552628.9035915710.3685545310.45310.459.6899811
8463686.809586738.027463686.809586738.027463686.809586738.027    463686.809586738.027501559.1099.117.5880452
Sheet1
Cell Formulas
RangeFormula
F4:F8,CR4:CR8,CI4:CI8,BZ4:BZ8,BQ4:BQ8,BH4:BH8,AY4:AY8,AP4:AP8,AG4:AG8,X4:X8,O4:O8F4=D4-E4
G4:G8,CS4:CS8,CJ4:CJ8,CA4:CA8,BR4:BR8,BI4:BI8,AZ4:AZ8,AQ4:AQ8,AH4:AH8,Y4:Y8,P4:P8G4=IFERROR(C4/F4,0)
DF4:DF8DF4=INDEX(B4:DE4,AGGREGATE(14,6,(COLUMN($B$3:$DE$3)-COLUMN($B$3)+1)/($B$3:$DE$3=$G$3)/(B4:DE4>0),1))
DG4:DG8DG4=SUMIFS(B4:DE4,$B$3:$DE$3,$C$3)/SUMIFS(B4:DE4,$B$3:$DE$3,$F$3)
Thanks, Fluff, I saw your post after I sent my reply. Thank you both for your help.
 

mr_ITtoyou

New Member
Joined
Mar 26, 2021
Messages
31
Office Version
  1. 2016
Platform
  1. Windows
Fluff, that is the formula worked great. Huge thank you for your help. Can I ask you for help for one more thing? How do I remove the #NUM! and #DIV/0! for cells that does not have any data? Do I enter the IFERROR somewhere and the ,0 at the end ?

1639787886176.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You can wrap them in iferror like
Excel Formula:
=IFERROR(SUMIFS(B4:DE4,$B$3:$DE$3,$C$3)/SUMIFS(B4:DE4,$B$3:$DE$3,$F$3),"")
 

mr_ITtoyou

New Member
Joined
Mar 26, 2021
Messages
31
Office Version
  1. 2016
Platform
  1. Windows
The spreadsheet looks a lot better now. Thanks Fluff for all or help. Happy Holidays
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,493
Messages
5,764,698
Members
425,231
Latest member
mramb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top