Making a benchmark or target 0

[RainerJonker]

I am busy making a Incident/Accident Report and i want to make a target 0, for example 0 incidents would be 100% and 2 incidents 80%. Can someone please show me how to create a formula for this.

 

[Fluff]

Hi & welcome to MrExcel.
How about

++Fluff.xlsm
	B	C
1
2	0	100%
3	1	90%
4	2	80%
5	3	70%
6	4	60%
7	5	50%
8	6	40%
Data

Cell Formulas
Range	Formula
C2:C8	C2	=100%-B2*0.1

 

[RainerJonker]

So the plan is that incidents will count a total of 9% towards Health and Safety, so I want to calculate per week what the incidents will contribute to the total Health and safety ( so basically like i said in my previous statement, 0 incidents would be 9% i.e. 100% contribution. And 1 incident 6%, 2 incidents 3% and 3 incidents 0%. In other words the safer you work the higher the contribution. I hope you understand and thanks for trying to help me.

SRP Test 1.xlsx
	A	B	C	D	E
1		Health and Safety
2
3		Incidents Reported
4
5			Benchmark	0
6			Weight	9,00%
7
8			Shift A:
9
10		Weeks:	Input Values:	% Towards Total:
11		1	3	=9%-(C11*0.1)
12		2		9,0
13		3		9,0
14		4		9,0
15		5		9,0
16		6		9,0
17		7		9,0
18		8		9,0
19		9		9,0
20		10		9,0
21		11		9,0
Sheet10

Cell Formulas
Range	Formula
D12:D21	D12	=(27-C12)/3

 

[Fluff]

In that case maybe

++Fluff.xlsm
	B	C
1
2	0	9%
3	1	6%
4	2	3%
5	3	0%
Data

Cell Formulas
Range	Formula
C2:C5	C2	=9%-B2*0.03

 

[RainerJonker]

In that case maybe

++Fluff.xlsm
	B	C
1
2	0	9%
3	1	6%
4	2	3%
5	3	0%
Data

Cell Formulas
Range	Formula
C2:C5	C2	=9%-B2*0.03

thank you

 

[Fluff]

You're welcome.