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

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

##### Well-known Member
Hi Mr_ITtoyou,

I'm having difficulty following what you want but let me throw a couple of things out there.

While averages of averages is always suspicious I do suspect that DF4 might be:
Excel Formula:
``=AVERAGEIFS(\$B4:\$DE4,\$B\$3:\$DE\$3,"Averaged Amount Of Calls Per Hour",\$B4:\$DE4,">0")``

Then DG4 would be
Excel Formula:
``=AVERAGEIFS(\$B4:\$DE4,\$B\$3:\$DE\$3,"Average Daily Registrations",\$B4:\$DE4,">0")``

If you only want the highest of a value then my Excel 2016 doesn't have MAXIFS but AGGREGATE should work
Excel Formula:
``=AGGREGATE(14,6,\$B4:\$DE4/(\$B\$3:\$DE\$3="Averaged Amount Of Calls Per Hour"),1)``

Not sure if those help...

#### Fluff

##### MrExcel MVP, Moderator
This should give you the true running average
Excel Formula:
``=SUMIFS(B4:DE4,\$B\$3:\$DE\$3,\$C\$3)/SUMIFS(B4:DE4,\$B\$3:\$DE\$3,\$F\$3)``

#### Fluff

##### MrExcel MVP, Moderator
If you want the most recent average, try
Excel Formula:
``=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))``

#### mr_ITtoyou

##### New Member
Sorry for the confusion Toadstool, I created a formula for each month, at the bottom that takes the Average Amount of Calls Per Hour numbers and divides by the number of months, Jan. 1, Feb. 2, March divided by 3, and so on, and have my results the cells at the bottom of the Months spreadsheet, shown below, Jan.- Dec. What I want to do is to automatically place the results of the latest month data for each agent, from Columns F, P, Y, AH, AQ, AZ, BI, BR, CA, CJ, CS, DB, Row 35-41 and place the latest result in column DF row 4-10, Agents 1-7. Is there a way where the data results for the month of Feb. could automatically override or replace Jan. data in Column DF row 4-10 and once March data is entered then those results in column Y, row 35-41 automatically replace the data in Column DF row 4-10 to the latest results? Thanks for your help

Jan. Average Call Per Hour Year to Date Column F Row 35-41
 Average Call Per Hour Year To date 6.27 Cell F35 7.52 Cell F36 8.22 Cell F37 8.9 Cell F38 6.81 Cell F39 12.71 Cell F41

Feb. Average Call Per Hour Year to Date column P row 35-41
 Average Call Per Hour Year To date 9.82 10.83 12.4 14.09 10.82 18.55

Column DF 4-10 latest result for Feb.
 Average Call Per Hour 9.82 10.83 12.4 14.09 10.82 18.55

Column DF 4-10 latest result for March
 Average Call Per Hour 15.47 16.65 19.33 22.24 17.11 28.62

#### Fluff

##### MrExcel MVP, Moderator
Are you trying to get the average year to date in col DF? If so try the formula I suggested in post#3 It will give you the true average.

Also the formula you have got in rows 35 to 41 are wrong. They should be like
Excel Formula:
``=(G4+P4+Y4)/3``
but that will give an average of averages, which you should not do.

#### mr_ITtoyou

##### New Member
Are you trying to get the average year to date in col DF? If so try the formula I suggested in post#3 It will give you the true average.

Also the formula you have got in rows 35 to 41 are wrong. They should be like
Excel Formula:
``=(G4+P4+Y4)/3``
but that will give an average of averages, which you should not do.
Yes. So I have a basic formula at the bottom of the month's spreadsheet, in rows 35-41, for each month, columns F, P, Y, AH, AQ, AZ, BI, BR, CA, CJ, CS, DB. As the data is entered in for each month, I want to have the results/data automatically placed in column DF rows 4-10. If (let's say) there is data in the cells DF row 4-10 from the month of March, and then April's is done, I would like to have the data from column AH row 35-41 placed in column DF row 4-10, automatically removing or replacing March data. I'll check out the formula in post #3. Thanks, Fluff.

#### Fluff

##### MrExcel MVP, Moderator
So I have a basic formula at the bottom of the month's spreadsheet, in rows 35-41, for each month,
But as I have pointed out those formulae are wrong & you should not average averages, which you are doing.

#### mr_ITtoyou

##### New Member
Yes. So I have a basic formula at the bottom of the month's spreadsheet, in rows 35-41, for each month, columns F, P, Y, AH, AQ, AZ, BI, BR, CA, CJ, CS, DB. As the data is entered in for each month, I want to have the results/data automatically placed in column DF rows 4-10. If (let's say) there is data in the cells DF row 4-10 from the month of March, and then April's is done, I would like to have the data from column AH row 35-41 placed in column DF row 4-10, automatically removing or replacing March data. I'll check out the formula in post #3. Thanks, Fluff.
I have updated the formula to this, that you pointed out, =(G4+P4+Y4)/3, thanks, Fluff. I'm working on adding the other formula, in post #3, Thanks.

#### mr_ITtoyou

##### New Member
Fluff, I am on the new side of all of these formulas, your formula =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))
The part with \$B\$3, (COLUMN(\$B\$3:\$DE\$3)-COLUMN(\$B\$3) is in the row of the text description not the cells with the data, should it be (COLUMN(\$B\$4:\$DE\$4)-COLUMN(\$B\$4), or is what you have correct?

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

1,186,807
Messages
5,959,912
Members
438,453
Latest member
NRG909

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