reduce totals by 25%

[Gonney]

Hi Team,

For privacy reasons I've recreated a dummy sheet. What I'm attempting to do is have a sheet that reduces Column H and Column K by 25% each if conditions are not met.

So if column H is less than 100% we then have 25% of the total of B reduced.
Thus if column J is less than 80% we then reduce by 25% of the total of B reduced.

I have put together a table below that shows the end results which have been manually calculated.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1		before deductions
2	Player name	Money earned	Met	Not met	Total	reg not met	Reg as Percent	if under 100% reduce 25% off total	Remaining balance after 25%	Quality	if under 80% reduce 25% off total	remaining balance After 80% rule	Total deductions	Total remaining payable
3	Bob	$365.50	3	0	3	0	100.00%	Equation needed	#VALUE!	100	Equation needed	#VALUE!	H + K	#VALUE!
4	Sally	$579.00	5	0	5	0	100.00%	Equation needed	#VALUE!	100	Equation needed	#VALUE!	H + K	#VALUE!
5	Fred	$716.00	2	4	6	1	83.33%	Equation needed	#VALUE!	33.33	Equation needed	#VALUE!	H + K	#VALUE!
6	Albert	$180.50	0	0	0	0	0.00%	Equation needed	#VALUE!	0	Equation needed	#VALUE!	H + K	#VALUE!
7
8
9	Player name	Money earned	Met	Not met	Total	reg not met	Reg as Percent	if under 100% reduce 25% off total	Remaining balance after 25%	Quality	if under 80% reduce 25% off total	remaining balance After 80% rule	Total deductions	Total remaining payable
10	Bob	$365.50	3	0	3	0	100.00%	0	$356.50	100	0	$365.50	0	$365.50
11	Sally	$579.00	5	0	5	0	100.00%	0	$579.00	100	0	$579.00	0	$579.00
12	Fred	$716.00	2	4	6	1	83.33%	179	$537.00	33.33	179	$537.00	358	$358.00
13	Albert	$180.50	0	0	0	0	0.00%	45.12	$135.38	0	45.12	$135.38	90.24	$90.26
14
Sheet1

Cell Formulas
Range	Formula
I3:I6,I12:I13	I3	=SUM(B3-H3)
L3:L6,L10:L13	L3	=SUM(B3-K3)
N3:N6,N10:N13	N3	=SUM(B3-M3)
M10:M13	M10	=SUM(H10 + K10)

Any Support on this would be greatly appreciated.

 

[bebo021999]

In H3:

=$B3*0.25*($G3<1)

Copy to K3, change <1 to new condition: <0.8

=$B3*0.25*($G3<0.8)

 

[Jimmypop]

Hi There

Something like below...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Player name	Money earned	Met	Not met	Total	reg not met	Reg as Percent	if under 100% reduce 25% off total	Remaining balance after 25%	Quality	if under 80% reduce 25% off total	remaining balance After 80% rule	Total deductions	Total remaining payable
2	Bob	$ 365.50	3	0	3	0	100%	0	$ 365.50	100%	0	$ 365.50	$ -	$ 365.50
3	Sally	$ 579.00	5	0	5	0	100%	0	$ 579.00	100%	0	$ 579.00	$ -	$ 579.00
4	Fred	$ 716.00	2	4	6	1	83%	$ 179.00	$ 537.00	33.33%	$ 179.00	$ 537.00	$ 358.00	$ 358.00
5	Albert	$ 180.50	0	0	0	0	0%	$ 45.13	$ 135.38	0%	$ 45.13	$ 135.38	$ 90.25	$ 90.25
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=IF(G2<1,B2*0.25,"0")
I2:I5	I2	=SUM(B2-H2)
K2:K5	K2	=IF(G2<1,B2*0.25,"0")
L2:L5	L2	=SUM(B2-K2)
M2:M5	M2	=SUM(H2+K2)
N2:N5	N2	=SUM(B2-M2)

 

[Gonney]

Thank you both for the response <3.