Formula to calculate the Average late deliver monthly and the Percentage

Lukma

Board Regular
Joined
Feb 12, 2020
Messages
110
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Hi Guy

please can anyone help out with this i was trying to calculate the Average of Late from Column EO and also find the Percentage, and also to sumifs monthly the total hours of Late and Ontime
So i Create a Column in from
Column EJ9 is the Month Dropdown
Column EK is Criteria List Name for Late and On Time

Appreciate if anyone could assist with this

Regards


ILSP KPI Performance Tracking Master Data-1.xlsx
EJEKELEMENEOEP
901-May-21Suppliers
10Total HoursLate1677:46
11Average OfLate13:58
12PercentageLate58%
13Total HoursOn Time
14Average OfOn Time
15PercentageOn Time
16
17MonthMus No VslVSL Start Jetty Date & Time Supplier Name Arrival Date & Time Delay HoursLater On Time
1801-May-21114913/05/21 08:30Schlumberger 13/05/21 09:100:40Late
1901-May-21114913/05/21 08:30Schlumberger 13/05/21 11:102:40Late
2001-May-21114913/05/21 08:30Uni Arab13/05/21 18:3010:00Late
2101-May-21114913/05/21 08:30Emdad13/05/21 17:008:30Late
2201-May-21114913/05/21 08:30WeatherFord 12/05/21 16:45-15:45On TIme
2301-May-21112010/05/21 21:45United Safety 09/05/21 11:00-10:45On TIme
2401-May-21112310/05/21 22:30Alphamed09/05/21 09:21-13:9On TIme
2501-May-21112511/05/21 06:36Halliburton11/05/21 10:454:09Late
2601-May-21112511/05/21 06:36Schlumberger 11/05/21 09:002:24Late
2701-May-21112511/05/21 06:36Al Ahlia10/05/21 11:00-19:36On TIme
2801-May-21112511/05/21 06:36Alphamed10/05/21 09:00-21:36On TIme
2901-May-21112611/05/21 07:00Halliburton10/05/21 12:00-19:0On TIme
3001-May-21112611/05/21 07:00Halliburton09/05/21 12:20-18:40On TIme
3101-May-21112611/05/21 07:00Averda10/05/21 09:40-21:20On TIme
3201-May-21112611/05/21 07:00WeatherFord 10/05/21 16:00-15:0On TIme
3301-May-21112711/05/21 14:20WeatherFord 10/05/21 14:30-23:50On TIme
3401-May-21112711/05/21 14:20Al Ahlia10/05/21 12:40-1:40On TIme
3501-May-21112711/05/21 14:20BJ Service 10/05/21 22:25-15:55On TIme
3601-May-21112711/05/21 14:20Sodexo / Kelvin11/05/21 16:101:50Late
3701-May-21112711/05/21 14:20Selective Marine11/05/21 20:105:50Late
3801-May-21112711/05/21 14:20Alphamed10/05/21 09:00-5:20On TIme
3901-May-21112711/05/21 14:20Stardford Marine11/05/21 20:556:35Late
4001-May-21113011/05/21 22:00AmBhertel LLc11/05/21 20:10-1:50On TIme
4101-May-21113212/05/21 00:18Al Ghaith11/05/21 09:30-14:48On TIme
4201-May-21113511/05/21 16:40International Tubular11/05/21 21:505:10Late
4301-May-21113511/05/21 16:40Al Ahlia11/05/21 16:450:05Late
4401-May-21113612/05/21 01:55WeatherFord 09/05/21 16:45-9:10On TIme
4501-May-21113612/05/21 01:55Alphamed11/05/21 09:30-16:25On TIme
4601-May-21113912/05/21 03:05ADNH11/05/21 12:50-14:15On TIme
4701-May-21113712/05/21 02:10Al Mansoori10/05/21 14:00-12:10On TIme
4801-May-21113712/05/21 02:10Alphamed11/05/21 00:20-1:50On TIme
4901-May-21113712/05/21 02:10Halliburton10/05/21 13:15-12:55On TIme
5001-May-21113712/05/21 02:10Al Ghaith10/05/21 11:30-14:40On TIme
5101-May-21113712/05/21 02:10Scomi11/05/21 12:00-14:10On TIme
5201-May-21114012/05/21 10:45WeatherFord 10/05/21 14:30-20:15On TIme
ILSP_KPI_Tracking_Data_Entry
Cell Formulas
RangeFormula
EL10EL10=SUMIFS($EO$18:$EO$60000,$EJ$18:$EJ$60000,$EJ$9,$EP$18:$EP$60000,$EK$10)
EL11EL11=AVERAGEIFS(EO18:EO60000,EJ18:EJ60000,EJ9,EO18:EO60000,">0",EP18:EP60000,EK10)
EL12EL12=EL11
EO18:EO52EO18=IFERROR(IF(EN18-EL18<0, "-" & TEXT(ABS(EN18-EL18),"h:m"), EN18-EL18),"")
EP18:EP52EP18=IF(EN18>EL18,"Late",IF(EN18<EL18,"On TIme",""))
EJ18:EJ52EJ18=IFERROR(EOMONTH(EL18,-1)+1,"")
EL18:EL52EL18=IFERROR(VLOOKUP(EK18,$DL$18:$DW$60002,10,0),"")
Cells with Data Validation
CellAllowCriteria
EJ9List=$ACT$18:$ACT$209
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

johnny51981

New Member
Joined
Jun 8, 2015
Messages
41
Hi @Lukma
Because your cells calculating your Late Total Hours, Average Of, and Percentage by including the formula pointing to the word "Late" in EK10 through EK12, respectively, you can simply copy and paste these 3 formulas in EL10 through EL12 and Paste Special Formulas into EL13 through EL15, and it will then be pointing to the words "On Time". HOWEVER, make sure to not make EK10 through EK12 are not absolute rows.

Example:
EL10 =SUMIFS($EO$18:$EO$60000,$EJ$18:$EJ$60000,$EJ$9,$EP$18:$EP$60000,$EK10) I removed the $ between EK and 10.
EL13 =SUMIFS($EO$18:$EO$60000,$EJ$18:$EJ$60000,$EJ$9,$EP$18:$EP$60000,$EK13) After removing the absolute from above, your copy and Paste will update accordingly and results received will be for "On Time"
 

Forum statistics

Threads
1,141,075
Messages
5,704,156
Members
421,330
Latest member
imdumb

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