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.

 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,430
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows
I agree with Toadstool, about average of averages.
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
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 26, 2021
Messages
31
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 26, 2021
Messages
31
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jun 12, 2014
Messages
67,759
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 26, 2021
Messages
31
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Mar 26, 2021
Messages
31
Office Version
  1. 2016
Platform
  1. Windows
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?
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
1,151,490
Messages
5,764,667
Members
425,228
Latest member
Rashid mahmood

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