Formula to Measure Time into Percentage Scoring

[rizkadiana]

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
	A	B	C	D	E
1	Timestamp	Name	Date	Store
2	01/09/2020 20:03	Angelica	01/09/2020	Apple
3	02/09/2020 21:36:11	Angelica	02/09/2020	Apple
4	03/09/2020 07:39:35	Eliza	01/09/2020	Lemon
5	03/09/2020 07:43	Eliza	02/09/2020	Lemon
6	03/09/2020 09:08	Peggy	01/09/2020	Durian
7	03/09/2020 09:16	Peggy	02/09/2020	Durian
8	03/09/2020 21:23	Angelica	03/09/2020	Apple
9	03/09/2020 21:41:28	Alexander	03/09/2020	Papaya
10	03/09/2020 22:57	Eliza	03/09/2020	Lemon
11
12
13	Name	Store	Date	Report Submission Score
14	Angelica	Apple	01/09/2020	100%
15	Angelica	Apple	02/09/2020	100%
16	Eliza	Lemon	02/09/2020	40%
17	Peggy	Durian	01/09/2020	0%
18	Alexander	Papaya	03/09/2020	100%
19
Sheet2

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

Thank you all for your help!

 

[AlanY]

try this
the lookup table in G1:H11 can be hidden or move to other sheet

Book1
	A	B	C	D	E	F	G	H	I
1	Timestamp	Name	Date	Store	Report Submission Score			0	100%
2	01/09/2020 20:03	Angelica	01/09/2020	Apple	100%		+2 hour :	2	90%
3	02/09/2020 21:36	Angelica	02/09/2020	Apple	100%		+4 hour :	4	80%
4	03/09/2020 07:39	Eliza	01/09/2020	Lemon	0%		+6 hour :	6	70%
5	03/09/2020 07:43	Eliza	02/09/2020	Lemon	60%		+7 hour :	7	60%
6	03/09/2020 09:08	Peggy	01/09/2020	Durian	0%		+8 hour :	8	50%
7	03/09/2020 09:16	Peggy	02/09/2020	Durian	40%		+9 hour :	9	40%
8	03/09/2020 21:23	Angelica	03/09/2020	Apple	100%		+10 hour:	10	30%
9	03/09/2020 21:41	Alexander	03/09/2020	Papaya	100%		+11 hour:	11	20%
10	03/09/2020 22:57	Eliza	03/09/2020	Lemon	100%		+12 hour:	12	10%
11							+14 hour:	14	0%
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=LOOKUP(MAX(0,24*(A2-C2)-24),$H$1:$H$11,$I$1:$I$11)

 

[rizkadiana]

Y

try this
the lookup table in G1:H11 can be hidden or move to other sheet

Book1
	A	B	C	D	E	F	G	H	I
1	Timestamp	Name	Date	Store	Report Submission Score			0	100%
2	01/09/2020 20:03	Angelica	01/09/2020	Apple	100%		+2 hour :	2	90%
3	02/09/2020 21:36	Angelica	02/09/2020	Apple	100%		+4 hour :	4	80%
4	03/09/2020 07:39	Eliza	01/09/2020	Lemon	0%		+6 hour :	6	70%
5	03/09/2020 07:43	Eliza	02/09/2020	Lemon	60%		+7 hour :	7	60%
6	03/09/2020 09:08	Peggy	01/09/2020	Durian	0%		+8 hour :	8	50%
7	03/09/2020 09:16	Peggy	02/09/2020	Durian	40%		+9 hour :	9	40%
8	03/09/2020 21:23	Angelica	03/09/2020	Apple	100%		+10 hour:	10	30%
9	03/09/2020 21:41	Alexander	03/09/2020	Papaya	100%		+11 hour:	11	20%
10	03/09/2020 22:57	Eliza	03/09/2020	Lemon	100%		+12 hour:	12	10%
11							+14 hour:	14	0%
Sheet1

Cell Formulas
Range	Formula
E2:E10	E2	=LOOKUP(MAX(0,24*(A2-C2)-24),$H$1:$H$11,$I$1:$I$11)

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)?

 

[AlanY]

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)?

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

 

[jasonb75]

but how about the criteria of name and stores can be applied?

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?

 

[rizkadiana]

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

Yes, okay I will state the requirements.

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?

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

 

[jasonb75]

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.

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%?

 

[rizkadiana]

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%?

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.

 

[rizkadiana]

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.

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.

 

[jasonb75]

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
	A	B	C	D	E	F	G	H	I
1	Timestamp	Name	Date	Store	Score			0	1
2	01/09/2020 20:03:59	Angelica	01/09/2020	Apple	100%		+2 hour :	2	0.9
3	02/09/2020 21:36:11	Angelica	02/09/2020	Apple	100%		+4 hour :	4	0.8
4	03/09/2020 07:39:35	Eliza	01/09/2020	Lemon	0%		+6 hour :	6	0.7
5	03/09/2020 07:43:25	Eliza	02/09/2020	Lemon	60%		+7 hour :	7	0.6
6	03/09/2020 09:08:17	Peggy	01/09/2020	Durian	0%		+8 hour :	8	0.5
7	03/09/2020 09:16:16	Peggy	02/09/2020	Durian	40%		+9 hour :	9	0.4
8	03/09/2020 21:23:32	Angelica	03/09/2020	Apple	100%		+10 hour:	10	0.3
9	03/09/2020 21:41:28	Alexander	03/09/2020	Papaya	100%		+11 hour:	11	0.2
10	03/09/2020 22:57:36	Eliza	03/09/2020	Lemon	100%		+12 hour:	12	0.1
11							+14 hour:	14	0
12	Store / Name	Average Score
13	Apple	100.00%
14	Angelica	100.00%
15	Durian	20.00%
16	Peggy	20.00%
17	Lemon	53.33%
18	Eliza	53.33%
19	Papaya	100.00%
20	Alexander	100.00%
21	Grand Total	66.67%
Sheet6

Cell Formulas
Range	Formula
E2:E10	E2	=LOOKUP(MAX(0,24*(A2-C2)-24),$H$1:$H$11,$I$1:$I$11)