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

[mr_ITtoyou]

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.

Excel Agent call Spreadsheet - Google Drive

drive.google.com

 

[Fluff]

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
	C			F	G					L			O	P					U			X	Y					AD			AG	AH					AM			AP	AQ					AV			AY	AZ					BE			BH	BI					BN			BQ	BR					BW			BZ	CA					CF			CI	CJ					CO			CR	CS													DF	DG
3	Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour													Last average	Averaged Amount Of Calls Per Hour in total
4	445			71	6.268					512			72	7.111					445			71	6.268					512			72	7.111					445			71	6.268					512			72	7.111																							445			71	6.268					512			72	7.111					390			61	6.393													6.39	6.6635071
5	820			109	7.523					629			95	6.621					820			109	7.523					629			95	6.621					820			109	7.523					629			95	6.621																							820			109	7.523					629			95	6.621					522			76	6.868													6.87	7.0829596
6	707			86	8.221					661			79	8.367					707			86	8.221					661			79	8.367					707			86	8.221					661			79	8.367																							707			86	8.221					661			79	8.367					649			68	9.544													9.54	8.407967
7	552			62	8.903					591			57	10.368					552			62	8.903					591			57	10.368					552			62	8.903					591			57	10.368																							552			62	8.903					591			57	10.368					554			53	10.453													10.45	9.6899811
8	463			68	6.809					586			73	8.027					463			68	6.809					586			73	8.027					463			68	6.809					586			73	8.027																							463			68	6.809					586			73	8.027					501			55	9.109													9.11	7.5880452
Sheet1

Cell Formulas
Range	Formula
F4:F8,CR4:CR8,CI4:CI8,BZ4:BZ8,BQ4:BQ8,BH4:BH8,AY4:AY8,AP4:AP8,AG4:AG8,X4:X8,O4:O8	F4	=D4-E4
G4:G8,CS4:CS8,CJ4:CJ8,CA4:CA8,BR4:BR8,BI4:BI8,AZ4:AZ8,AQ4:AQ8,AH4:AH8,Y4:Y8,P4:P8	G4	=IFERROR(C4/F4,0)
DF4:DF8	DF4	=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:DG8	DG4	=SUMIFS(B4:DE4,$B$3:$DE$3,$C$3)/SUMIFS(B4:DE4,$B$3:$DE$3,$F$3)

 

[mr_ITtoyou]

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]

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
	C			F	G					L			O	P					U			X	Y					AD			AG	AH					AM			AP	AQ					AV			AY	AZ					BE			BH	BI					BN			BQ	BR					BW			BZ	CA					CF			CI	CJ					CO			CR	CS													DF	DG
3	Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour					Total Calls Handled			Month Total Hours Login to Queue	Averaged Amount Of Calls Per Hour													Last average	Averaged Amount Of Calls Per Hour in total
4	445			71	6.268					512			72	7.111					445			71	6.268					512			72	7.111					445			71	6.268					512			72	7.111																							445			71	6.268					512			72	7.111					390			61	6.393													6.39	6.6635071
5	820			109	7.523					629			95	6.621					820			109	7.523					629			95	6.621					820			109	7.523					629			95	6.621																							820			109	7.523					629			95	6.621					522			76	6.868													6.87	7.0829596
6	707			86	8.221					661			79	8.367					707			86	8.221					661			79	8.367					707			86	8.221					661			79	8.367																							707			86	8.221					661			79	8.367					649			68	9.544													9.54	8.407967
7	552			62	8.903					591			57	10.368					552			62	8.903					591			57	10.368					552			62	8.903					591			57	10.368																							552			62	8.903					591			57	10.368					554			53	10.453													10.45	9.6899811
8	463			68	6.809					586			73	8.027					463			68	6.809					586			73	8.027					463			68	6.809					586			73	8.027																							463			68	6.809					586			73	8.027					501			55	9.109													9.11	7.5880452
Sheet1

Cell Formulas
Range	Formula
F4:F8,CR4:CR8,CI4:CI8,BZ4:BZ8,BQ4:BQ8,BH4:BH8,AY4:AY8,AP4:AP8,AG4:AG8,X4:X8,O4:O8	F4	=D4-E4
G4:G8,CS4:CS8,CJ4:CJ8,CA4:CA8,BR4:BR8,BI4:BI8,AZ4:AZ8,AQ4:AQ8,AH4:AH8,Y4:Y8,P4:P8	G4	=IFERROR(C4/F4,0)
DF4:DF8	DF4	=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:DG8	DG4	=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]

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 ?

 

[Fluff]

You can wrap them in iferror like

=IFERROR(SUMIFS(B4:DE4,$B$3:$DE$3,$C$3)/SUMIFS(B4:DE4,$B$3:$DE$3,$F$3),"")

 

[mr_ITtoyou]

The spreadsheet looks a lot better now. Thanks Fluff for all or help. Happy Holidays

 

[Fluff]

Glad we could help & thanks for the feedback.