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

 

[Toadstool]

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:

=AVERAGEIFS($B4:$DE4,$B$3:$DE$3,"Averaged Amount Of Calls Per Hour",$B4:$DE4,">0")

Then DG4 would be

=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

=AGGREGATE(14,6,$B4:$DE4/($B$3:$DE$3="Averaged Amount Of Calls Per Hour"),1)

Not sure if those help...

 

[Fluff]

I agree with Toadstool, about average of averages.
This should give you the true running average

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

 

[Fluff]

If you want the most recent average, try

=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]

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]

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

=(G4+P4+Y4)/3

but that will give an average of averages, which you should not do.

 

[mr_ITtoyou]

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

=(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]

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]

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]

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?