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

#### mr_ITtoyou

##### New Member
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
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
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)

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### mr_ITtoyou

##### New Member
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
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
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
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

##### MrExcel MVP, Moderator
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
The spreadsheet looks a lot better now. Thanks Fluff for all or help. Happy Holidays

#### Fluff

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
8
Views
232
Replies
1
Views
67
Replies
9
Views
181
Replies
4
Views
140
Replies
4
Views
36

1,186,808
Messages
5,959,938
Members
438,455
Latest member
Beverly Jarrell

### 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.

### Which adblocker are you using?

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

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