Formula to Measure Time into Percentage Scoring

R

rizkadiana

New Member
Joined
Sep 5, 2020
Messages
18
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone! I hope you all have a pleasant day.

I do not know if this is possible in Excel, but I would like to ask.

We have a KPI for report submission deadline.
The rule is, maximum time for submission is at 00.00 in the same day of working day. This would score a 100%.
After exceeding that time limit/deadline, the score would go down to 90%, 80% and so on.
My idea of the rule is as follows:
+2 hour : 02.00 = 90%
+4 hour : 04.00 = 80%
+6 hour : 05.00 = 70%
+7 hour : 06.00 = 60%
+8 hour : 07.00 = 50%
+9 hour : 08.00 = 40%
+10 hour: 09.00 = 30%
+11 hour: 10.00 = 20%
+12 hour: 11.00 = 10%
+14 hour: 12.00 = 0%
and after that it will count as 0%.

SPG Evaluation Form.xlsx
ABCDE
1TimestampNameDateStore
201/09/2020 20:03Angelica01/09/2020Apple
302/09/2020 21:36:11Angelica02/09/2020Apple
403/09/2020 07:39:35Eliza01/09/2020Lemon
503/09/2020 07:43Eliza02/09/2020Lemon
603/09/2020 09:08Peggy01/09/2020Durian
703/09/2020 09:16Peggy02/09/2020Durian
803/09/2020 21:23Angelica03/09/2020Apple
903/09/2020 21:41:28Alexander03/09/2020Papaya
1003/09/2020 22:57Eliza03/09/2020Lemon
11
12
13NameStoreDateReport Submission Score
14AngelicaApple01/09/2020100%
15AngelicaApple02/09/2020100%
16ElizaLemon02/09/202040%
17PeggyDurian01/09/20200%
18AlexanderPapaya03/09/2020100%
19
Sheet2


Here is the example and expected answer.
Is it possible?

Thank you all for your help!
 
Sort by date Sort by votes
jasonb75 said:
As far as I can see @AlanY has provided what you asked for. You should then be able to summarise the data with a pivot table (see below).

If this is not what you need then I suggest that you post a more accurate example of the source data and expected results with any criteria appled using XL2BB rather than a hit and miss selection.

Book1
ABCDEFGHI
1TimestampNameDateStoreScore01
201/09/2020 20:03:59Angelica01/09/2020Apple100%+2 hour :20.9
302/09/2020 21:36:11Angelica02/09/2020Apple100%+4 hour :40.8
403/09/2020 07:39:35Eliza01/09/2020Lemon0%+6 hour :60.7
503/09/2020 07:43:25Eliza02/09/2020Lemon60%+7 hour :70.6
603/09/2020 09:08:17Peggy01/09/2020Durian0%+8 hour :80.5
703/09/2020 09:16:16Peggy02/09/2020Durian40%+9 hour :90.4
803/09/2020 21:23:32Angelica03/09/2020Apple100%+10 hour:100.3
903/09/2020 21:41:28Alexander03/09/2020Papaya100%+11 hour:110.2
1003/09/2020 22:57:36Eliza03/09/2020Lemon100%+12 hour:120.1
11+14 hour:140
12Store / NameAverage Score
13Apple100.00%
14Angelica100.00%
15Durian20.00%
16Peggy20.00%
17Lemon53.33%
18Eliza53.33%
19Papaya100.00%
20Alexander100.00%
21Grand Total66.67%
Sheet6
Cell Formulas
RangeFormula
E2:E10E2=LOOKUP(MAX(0,24*(A2-C2)-24),$H$1:$H$11,$I$1:$I$11)
Click to expand...

I actually wanted to know in the course of a period, what is the average score of submission report time for an employee (field sales).
So I can measure their KPI, individually.
I need the answer to be average for each individual.

However, each employee can be responsible in more than 1 stores, therefor I wanted to make sure that the average score number is specific to each name and stores. (hence, the criteria is name and store)

This is the accurate dataset: (but with stores name redacted)
Note. That currently, each employee only have 1 store in their responsibility. But in the future, this things could change (they can be required to handle more than 1 stores). Therefor, I would like to prepare for that.

SPG Evaluation Form.xlsx
ABCDEFGHIJK
1TimestampNameDateStoreKPI Calculation: Report Submission Punctuality
201/09/2020 20:03Angel01/09/2020AEON
302/09/2020 21:36:11Angel02/09/2020AEONNoNameStoreTargetActualAchievement
403/09/2020 07:39:35Trish01/09/2020Wallmart G1OliviaFruit Store100%=
503/09/2020 07:43Trish02/09/2020Wallmart G2AstridDiamon100%
603/09/2020 09:08Olivia01/09/2020Fruit Store3CharlieTarget P100%
703/09/2020 09:16Olivia02/09/2020Fruit Store4ZettyTarget E100%
803/09/2020 21:23Angel03/09/2020AEON7AngelAEON100%
903/09/2020 21:41:28Zetty03/09/2020Target E8GabbyWallmart D100%
1003/09/2020 22:57Trish03/09/2020Wallmart G9TrishWallmart G100%
1104/09/2020 06:26Gabby01/09/2020Wallmart D
1204/09/2020 06:30Gabby02/09/2020Wallmart D
1304/09/2020 06:33Gabby03/09/2020Wallmart D
1404/09/2020 06:46Olivia03/09/2020Fruit Store
1504/09/2020 10:11Charlie03/09/2020Target P
1604/09/2020 10:41Astrid03/09/2020Diamond
1704/09/2020 20:40Zetty04/09/2020Target E
1804/09/2020 21:23Angel04/09/2020AEON
1904/09/2020 22:31:50Charlie04/09/2020Target P
2005/09/2020 00:06Trish04/09/2020Wallmart G
2105/09/2020 18:45Gabby04/09/2020Wallmart D
2205/09/2020 18:49Gabby05/09/2020Wallmart D
2305/09/2020 21:26Angel05/09/2020AEON
2405/09/2020 22:44:55Zetty05/09/2020Target E
2505/09/2020 22:51:07Charlie05/09/2020Target P
2606/09/2020 07:41Olivia05/09/2020Fruit Store
2706/09/2020 07:48Olivia04/09/2020Fruit Store
Sheet1



Thank you.
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
ok, try this

Book1
ABCDEFGHIJKLM
1TimestampNameDateStoreKPI Calculation: Report Submission Punctuality
201/09/2020 20:03Angel01/09/2020AEON100%0100%
302/09/2020 21:36Angel02/09/2020AEON100%NoNameStoreTargetActual290%
403/09/2020 07:39Trish01/09/2020Wallmart G0%1OliviaFruit Store100%34%480%
503/09/2020 07:43Trish02/09/2020Wallmart G60%2AstridDiamond100%30%670%
603/09/2020 09:08Olivia01/09/2020Fruit Store0%3CharlieTarget P100%77%760%
703/09/2020 09:16Olivia02/09/2020Fruit Store40%4ZettyTarget E100%100%850%
803/09/2020 21:23Angel03/09/2020AEON100%7AngelAEON100%100%940%
903/09/2020 21:41Zetty03/09/2020Target E100%8GabbyWallmart D100%34%1030%
1003/09/2020 22:57Trish03/09/2020Wallmart G100%9TrishWallmart G100%65%1120%
1104/09/2020 06:26Gabby01/09/2020Wallmart D0%1210%
1204/09/2020 06:30Gabby02/09/2020Wallmart D0%140%
1304/09/2020 06:33Gabby03/09/2020Wallmart D70%
1404/09/2020 06:46Olivia03/09/2020Fruit Store70%
1504/09/2020 10:11Charlie03/09/2020Target P30%
1604/09/2020 10:41Astrid03/09/2020Diamond30%
1704/09/2020 20:40Zetty04/09/2020Target E100%
1804/09/2020 21:23Angel04/09/2020AEON100%
1904/09/2020 22:31Charlie04/09/2020Target P100%
2005/09/2020 00:06Trish04/09/2020Wallmart G100%
2105/09/2020 18:45Gabby04/09/2020Wallmart D0%
2205/09/2020 18:49Gabby05/09/2020Wallmart D100%
2305/09/2020 21:26Angel05/09/2020AEON100%
2405/09/2020 22:44Zetty05/09/2020Target E100%
2505/09/2020 22:51Charlie05/09/2020Target P100%
2606/09/2020 07:41Olivia05/09/2020Fruit Store60%
2706/09/2020 07:48Olivia04/09/2020Fruit Store0%
Sheet1
Cell Formulas
RangeFormula
J4:J10J4=AVERAGEIFS(E:E,B:B,G4,D:D,H4)
E2:E27E2=LOOKUP(MAX(0,24*(A2-C2)-24),Sheet1!$L$2:$L$12,Sheet1!$M$2:$M$12)
 
Upvote 0
if your version of excel doesn't has the averageif() function, use this alt array formula instead

Book1
ABCDEFGHIJKLM
1TimestampNameDateStoreKPI Calculation: Report Submission Punctuality
201/09/2020 20:03Angel01/09/2020AEON100%0100%
302/09/2020 21:36Angel02/09/2020AEON100%NoNameStoreTargetActual290%
403/09/2020 07:39Trish01/09/2020Wallmart G0%1OliviaFruit Store100%34%480%
503/09/2020 07:43Trish02/09/2020Wallmart G60%2AstridDiamond100%30%670%
603/09/2020 09:08Olivia01/09/2020Fruit Store0%3CharlieTarget P100%77%760%
703/09/2020 09:16Olivia02/09/2020Fruit Store40%4ZettyTarget E100%100%850%
803/09/2020 21:23Angel03/09/2020AEON100%7AngelAEON100%100%940%
903/09/2020 21:41Zetty03/09/2020Target E100%8GabbyWallmart D100%34%1030%
1003/09/2020 22:57Trish03/09/2020Wallmart G100%9TrishWallmart G100%65%1120%
1104/09/2020 06:26Gabby01/09/2020Wallmart D0%1210%
1204/09/2020 06:30Gabby02/09/2020Wallmart D0%140%
1304/09/2020 06:33Gabby03/09/2020Wallmart D70%
1404/09/2020 06:46Olivia03/09/2020Fruit Store70%
1504/09/2020 10:11Charlie03/09/2020Target P30%
1604/09/2020 10:41Astrid03/09/2020Diamond30%
1704/09/2020 20:40Zetty04/09/2020Target E100%
1804/09/2020 21:23Angel04/09/2020AEON100%
1904/09/2020 22:31Charlie04/09/2020Target P100%
2005/09/2020 00:06Trish04/09/2020Wallmart G100%
2105/09/2020 18:45Gabby04/09/2020Wallmart D0%
2205/09/2020 18:49Gabby05/09/2020Wallmart D100%
2305/09/2020 21:26Angel05/09/2020AEON100%
2405/09/2020 22:44Zetty05/09/2020Target E100%
2505/09/2020 22:51Charlie05/09/2020Target P100%
2606/09/2020 07:41Olivia05/09/2020Fruit Store60%
2706/09/2020 07:48Olivia04/09/2020Fruit Store0%
28
Sheet1
Cell Formulas
RangeFormula
J4:J10J4=AVERAGE(IF($B$2:$B$27=G4,IF($D$2:$D$27=H4,$E$2:$E$27)))
E2:E27E2=LOOKUP(MAX(0,24*(A2-C2)-24),Sheet1!$L$2:$L$12,Sheet1!$M$2:$M$12)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
AlanY said:
ok, try this

Book1
ABCDEFGHIJKLM
1TimestampNameDateStoreKPI Calculation: Report Submission Punctuality
201/09/2020 20:03Angel01/09/2020AEON100%0100%
302/09/2020 21:36Angel02/09/2020AEON100%NoNameStoreTargetActual290%
403/09/2020 07:39Trish01/09/2020Wallmart G0%1OliviaFruit Store100%34%480%
503/09/2020 07:43Trish02/09/2020Wallmart G60%2AstridDiamond100%30%670%
603/09/2020 09:08Olivia01/09/2020Fruit Store0%3CharlieTarget P100%77%760%
703/09/2020 09:16Olivia02/09/2020Fruit Store40%4ZettyTarget E100%100%850%
803/09/2020 21:23Angel03/09/2020AEON100%7AngelAEON100%100%940%
903/09/2020 21:41Zetty03/09/2020Target E100%8GabbyWallmart D100%34%1030%
1003/09/2020 22:57Trish03/09/2020Wallmart G100%9TrishWallmart G100%65%1120%
1104/09/2020 06:26Gabby01/09/2020Wallmart D0%1210%
1204/09/2020 06:30Gabby02/09/2020Wallmart D0%140%
1304/09/2020 06:33Gabby03/09/2020Wallmart D70%
1404/09/2020 06:46Olivia03/09/2020Fruit Store70%
1504/09/2020 10:11Charlie03/09/2020Target P30%
1604/09/2020 10:41Astrid03/09/2020Diamond30%
1704/09/2020 20:40Zetty04/09/2020Target E100%
1804/09/2020 21:23Angel04/09/2020AEON100%
1904/09/2020 22:31Charlie04/09/2020Target P100%
2005/09/2020 00:06Trish04/09/2020Wallmart G100%
2105/09/2020 18:45Gabby04/09/2020Wallmart D0%
2205/09/2020 18:49Gabby05/09/2020Wallmart D100%
2305/09/2020 21:26Angel05/09/2020AEON100%
2405/09/2020 22:44Zetty05/09/2020Target E100%
2505/09/2020 22:51Charlie05/09/2020Target P100%
2606/09/2020 07:41Olivia05/09/2020Fruit Store60%
2706/09/2020 07:48Olivia04/09/2020Fruit Store0%
Sheet1
Cell Formulas
RangeFormula
J4:J10J4=AVERAGEIFS(E:E,B:B,G4,D:D,H4)
E2:E27E2=LOOKUP(MAX(0,24*(A2-C2)-24),Sheet1!$L$2:$L$12,Sheet1!$M$2:$M$12)
Click to expand...

YES! I understand how this works now! Thank you so much, Alan!
Thank you for assisting and explaining things in detail!


AlanY said:
if your version of excel doesn't has the averageif() function, use this alt array formula instead

Book1
ABCDEFGHIJKLM
1TimestampNameDateStoreKPI Calculation: Report Submission Punctuality
201/09/2020 20:03Angel01/09/2020AEON100%0100%
302/09/2020 21:36Angel02/09/2020AEON100%NoNameStoreTargetActual290%
403/09/2020 07:39Trish01/09/2020Wallmart G0%1OliviaFruit Store100%34%480%
503/09/2020 07:43Trish02/09/2020Wallmart G60%2AstridDiamond100%30%670%
603/09/2020 09:08Olivia01/09/2020Fruit Store0%3CharlieTarget P100%77%760%
703/09/2020 09:16Olivia02/09/2020Fruit Store40%4ZettyTarget E100%100%850%
803/09/2020 21:23Angel03/09/2020AEON100%7AngelAEON100%100%940%
903/09/2020 21:41Zetty03/09/2020Target E100%8GabbyWallmart D100%34%1030%
1003/09/2020 22:57Trish03/09/2020Wallmart G100%9TrishWallmart G100%65%1120%
1104/09/2020 06:26Gabby01/09/2020Wallmart D0%1210%
1204/09/2020 06:30Gabby02/09/2020Wallmart D0%140%
1304/09/2020 06:33Gabby03/09/2020Wallmart D70%
1404/09/2020 06:46Olivia03/09/2020Fruit Store70%
1504/09/2020 10:11Charlie03/09/2020Target P30%
1604/09/2020 10:41Astrid03/09/2020Diamond30%
1704/09/2020 20:40Zetty04/09/2020Target E100%
1804/09/2020 21:23Angel04/09/2020AEON100%
1904/09/2020 22:31Charlie04/09/2020Target P100%
2005/09/2020 00:06Trish04/09/2020Wallmart G100%
2105/09/2020 18:45Gabby04/09/2020Wallmart D0%
2205/09/2020 18:49Gabby05/09/2020Wallmart D100%
2305/09/2020 21:26Angel05/09/2020AEON100%
2405/09/2020 22:44Zetty05/09/2020Target E100%
2505/09/2020 22:51Charlie05/09/2020Target P100%
2606/09/2020 07:41Olivia05/09/2020Fruit Store60%
2706/09/2020 07:48Olivia04/09/2020Fruit Store0%
28
Sheet1
Cell Formulas
RangeFormula
J4:J10J4=AVERAGE(IF($B$2:$B$27=G4,IF($D$2:$D$27=H4,$E$2:$E$27)))
E2:E27E2=LOOKUP(MAX(0,24*(A2-C2)-24),Sheet1!$L$2:$L$12,Sheet1!$M$2:$M$12)
Press CTRL+SHIFT+ENTER to enter array formulas.
Click to expand...

Thank you! I can use the averageifs formula, but thank you so much for providing alternatives.

I really appreciated your help and the effort you took to understand my problem, hope you have a great day!!
 
Upvote 0
rizkadiana said:
YES! I understand how this works now! Thank you so much, Alan!
Thank you for assisting and explaining things in detail!




Thank you! I can use the averageifs formula, but thank you so much for providing alternatives.

I really appreciated your help and the effort you took to understand my problem, hope you have a great day!!
Click to expand...
You’re welcome
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Formula to split date and time and work out energy usage trend
Replies
6
Views
460
Sundance_Kid
S
L
  • Question Question
Help with formula to capture a KPI
Replies
2
Views
646
Lukma
L
S
  • Question Question
Date and Time Formulas
2 3 4
Replies
34
Views
3K
awoohaw
awoohaw
T
  • Question Question
Using a dynamic formula to calculate value of reinvested dividends over changing time periods
Replies
2
Views
3K
Tuckbox
T
P
  • Question Question
Correcting Data
Replies
0
Views
562
parvezs27
P

Forum statistics

Threads
1,223,893
Messages
6,175,241
Members
452,622
Latest member
Laura_PinksBTHFT

Share this page

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
Back
Top