Setting a cap on %

[RainerJonker]

The score is out of 10, so when the input value is above 10 I still want the % to stay at 8,5%

SRP Test 1.xlsx
	A	B	C	D	E
1		Health and Safety
2
3		Planned Task Observations
4
5			Benchmark	10
6			Weight	8,50%
7
8			Shift A:
9
10		Weeks:	Input Values:	% Towards Total:
11		1	4	3,40%
12		2	1	0,85%
13		3	2	1,70%
14		4	0	0,00%
15		5	12	10,20%
16		6		0,00%
Sheet13

Cell Formulas
Range	Formula
D11:D16	D11	=C11/$D$5*$D$6

 

[Kerryx]

workbook test.xlsx
	B	C	D
10	Weeks:	Input Values:	% Towards Total:
11	1	4	3.40%
12	2	1	0.85%
13	3	2	1.70%
14	4	0	0.00%
15	5	12	8.50%
16	6		0.00%
Sheet7

Cell Formulas
Range	Formula
D11:D16	D11	=IF(C11>=10,8.5%,C11/$D$5*$D$6)

 

[RainerJonker]

workbook test.xlsx
	B	C	D
10	Weeks:	Input Values:	% Towards Total:
11	1	4	3.40%
12	2	1	0.85%
13	3	2	1.70%
14	4	0	0.00%
15	5	12	8.50%
16	6		0.00%
Sheet7

Cell Formulas
Range	Formula
D11:D16	D11	=IF(C11>=10,8.5%,C11/$D$5*$D$6)

Thank you very much!

 

[RainerJonker]

workbook test.xlsx
	B	C	D
10	Weeks:	Input Values:	% Towards Total:
11	1	4	3.40%
12	2	1	0.85%
13	3	2	1.70%
14	4	0	0.00%
15	5	12	8.50%
16	6		0.00%
Sheet7

Cell Formulas
Range	Formula
D11:D16	D11	=IF(C11>=10,8.5%,C11/$D$5*$D$6)

How do I change it so my , and . are like yours?

 

[Kerryx]

You just need to update the formula( either copy/paste mine or re-type yours) in cell D11 and fill down to D16, ensure cells are set to display as percentage value

 

[Fluff]

Another option is

=MIN(8.5%,C11/$D$5*$D$6)

 

[RainerJonker]

You just need to update the formula( either copy/paste mine or re-type yours) in cell D11 and fill down to D16, ensure cells are set to display as percentage value

View attachment 65028

Yes but mine works like 8,5% and yours 8.5%

 

[Kerryx]

Sorry didn't see your follow up did you get it sorted - The default display of commas or decimal points is based on your global system settings (Regional Settings in the Control Panel) and Excel Options. Or you can change it in Excel Options by unticking the system separators option