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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
try this
the lookup table in G1:H11 can be hidden or move to other sheet

Book1
ABCDEFGHI
1TimestampNameDateStoreReport Submission Score0100%
201/09/2020 20:03Angelica01/09/2020Apple100%+2 hour :290%
302/09/2020 21:36Angelica02/09/2020Apple100%+4 hour :480%
403/09/2020 07:39Eliza01/09/2020Lemon0%+6 hour :670%
503/09/2020 07:43Eliza02/09/2020Lemon60%+7 hour :760%
603/09/2020 09:08Peggy01/09/2020Durian0%+8 hour :850%
703/09/2020 09:16Peggy02/09/2020Durian40%+9 hour :940%
803/09/2020 21:23Angelica03/09/2020Apple100%+10 hour:1030%
903/09/2020 21:41Alexander03/09/2020Papaya100%+11 hour:1120%
1003/09/2020 22:57Eliza03/09/2020Lemon100%+12 hour:1210%
11+14 hour:140%
Sheet1
Cell Formulas
RangeFormula
E2:E10E2=LOOKUP(MAX(0,24*(A2-C2)-24),$H$1:$H$11,$I$1:$I$11)
 
Upvote 0
Y
AlanY said:
try this
the lookup table in G1:H11 can be hidden or move to other sheet

Book1
ABCDEFGHI
1TimestampNameDateStoreReport Submission Score0100%
201/09/2020 20:03Angelica01/09/2020Apple100%+2 hour :290%
302/09/2020 21:36Angelica02/09/2020Apple100%+4 hour :480%
403/09/2020 07:39Eliza01/09/2020Lemon0%+6 hour :670%
503/09/2020 07:43Eliza02/09/2020Lemon60%+7 hour :760%
603/09/2020 09:08Peggy01/09/2020Durian0%+8 hour :850%
703/09/2020 09:16Peggy02/09/2020Durian40%+9 hour :940%
803/09/2020 21:23Angelica03/09/2020Apple100%+10 hour:1030%
903/09/2020 21:41Alexander03/09/2020Papaya100%+11 hour:1120%
1003/09/2020 22:57Eliza03/09/2020Lemon100%+12 hour:1210%
11+14 hour:140%
Sheet1
Cell Formulas
RangeFormula
E2:E10E2=LOOKUP(MAX(0,24*(A2-C2)-24),$H$1:$H$11,$I$1:$I$11)
Click to expand...

Thank you for your response!

Yes, the formula shows the percentage, but how about the criteria of name and stores can be applied?
What if after that I want to calculate the average submission score (in percentage)?
 
Upvote 0
rizkadiana said:
Y


Thank you for your response!

Yes, the formula shows the percentage, but how about the criteria of name and stores can be applied?
What if after that I want to calculate the average submission score (in percentage)?
Click to expand...

not quite sure of your question, the percentages are based on Name (Col B) and Store (Col D).
could you give an example of your requirement
 
Upvote 0
rizkadiana said:
but how about the criteria of name and stores can be applied?
Click to expand...
I was looking at this earlier but didn't post as my answer was almost identical to Alan's.

I don't see that there is any reference to criteria in your question, the expected answer that you show only appears to be a random selection from the first table.
Why two rows of results for Angelica but only one for Eliza?
How do you decide which rows should be selected from the table to be shown in the results?
 
Upvote 0
AlanY said:
not quite sure of your question, the percentages are based on Name (Col B) and Store (Col D).
could you give an example of your requirement
Click to expand...

Yes, okay I will state the requirements.



jasonb75 said:
I was looking at this earlier but didn't post as my answer was almost identical to Alan's.

I don't see that there is any reference to criteria in your question, the expected answer that you show only appears to be a random selection from the first table.
Why two rows of results for Angelica but only one for Eliza?
How do you decide which rows should be selected from the table to be shown in the results?
Click to expand...

Yes, I did not include all the expected answer because it is meant to be just an example. In actuality, I need the answer all specific.


The formula is required to be:
1. Based on two criteria: Name and stores.
2.The scoring is tightly affected by the dates. Because it should be on the same date max on 00.00. Day after the score drops.

Thank you
 
Upvote 0
rizkadiana said:
2.The scoring is tightly affected by the dates. Because it should be on the same date max on 00.00. Day after the score drops.
Click to expand...
Could you clarify if 00:00 should be 100% or 90%?

00:00:00 is not the end of today, it is the start of tomorrow.

Similarly for other times, should 02:00:00 be 90% or 80%?
 
Upvote 0
jasonb75 said:
Could you clarify if 00:00 should be 100% or 90%?

00:00:00 is not the end of today, it is the start of tomorrow.

Similarly for other times, should 02:00:00 be 90% or 80%?
Click to expand...

Sorry for the mistake. Let me correct it.
00:00:00 is 100%
02:00:00 is 90%
04:00:00 is 80%
---------------------- end of 2 hour interval
---------------------- start 1 hour interval
05:00:00 is 70%
06:00:00 is 60%
07:00:00 is 50%
08:00:00 is 40%
09:00:00 is 30%
10:00:00 is 20%
11:00:00 is 10%
12:00:00 is 0%
---------------------- after this it will be 0%

Thank you.
 
Upvote 0
rizkadiana said:
Sorry for the mistake. Let me correct it.
00:00:00 is 100%
02:00:00 is 90%
04:00:00 is 80%
---------------------- end of 2 hour interval
---------------------- start 1 hour interval
05:00:00 is 70%
06:00:00 is 60%
07:00:00 is 50%
08:00:00 is 40%
09:00:00 is 30%
10:00:00 is 20%
11:00:00 is 10%
12:00:00 is 0%
---------------------- after this it will be 0%

Thank you.
Click to expand...

Example:
If Angelica input work date at 2 Sept 2020.
Her time limit to get the score of 100% is by submitting the report no later than 00:00:00 3 Sept 2020.
90% if she were to submit at 00:00:01 to 02:00:00
and so on.
 
Upvote 0
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question
Help with formula to capture a KPI
Replies
2
Views
489
Lukma
L
S
  • Question
Date and Time Formulas
2 3 4
Replies
34
Views
2K
awoohaw
awoohaw
T
  • Question
Using a dynamic formula to calculate value of reinvested dividends over changing time periods
Replies
2
Views
2K
Tuckbox
T
P
  • Question
Correcting Data
Replies
0
Views
455
parvezs27
P
O
  • Question
help with time formula please
Replies
6
Views
375
orsm6
O

Forum statistics

Threads
1,215,388
Messages
6,124,659
Members
449,178
Latest member
Emilou

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