Stop average calculation when cell value is less than a certain number

[HullBorn]

Hi all,

I'm creating a dart scorer for my dart team.

We stop working out the player averages when the remaining score gets below 140. In the example below the remaining score is E1

e.g if the remaining score when it's player 2s turn is 145 his score wants to be counted towards his average. If he scores 6 or more the taking the remaining score below 140 the scores from then on are not counted on the averages. Hope that makes some sort of sense.

Would someone be able to help me with the formula to do this?

Thanks Wayne.

Darts Scorer v1.xlsx
	B	C	D	E	F	G	H	I	J	K
1				308
2	Team 1									1001
3	Throws	Player 1	Player 2	Player 3	Player 4	Player 5	Player 6	Round	1001
4
5	1	100	45	90	56	56	100	447	554
6	2	50	20	65	45	45	21	246	308
7	3
8	4
9	5
10	6
11	7
12	8
13	9
14	10
15	11
16	12
17	13
18	14
19	15
20	16
21	17
22	18
23	19
24	20
25	21
26	22
27	23
28	24
29	25
30	26
31	27
32	28
33	29
34	30
35	Averages	75.00	32.50	77.50	50.50	50.50	60.50	346.50
Game 1

Cell Formulas
Range	Formula
E1	E1	=IF(COUNT(J5:J34)>0,MIN(J5:J34),K2)
J3	J3	=K2
I5:I34	I5	=IF(COUNT(C5:H5)>0,SUM(C5:H5),"")
J5	J5	=IF(LEN(I5)>0,IF(I5>J3,J3,IF(AND($B$1="Double",(J3-I5)=1),J3,J3-I5)),"")
J6:J34	J6	=IF(LEN(I6)>0,IF(I6>J5,J5,IF(AND($B$1="Double",(J5-I6)=1),J5,J5-I6)),"")
C35:H35	C35	=IFERROR(AVERAGE(C2:C29)+C32,"0")
I35	I35	=IFERROR(AVERAGE(I2:I29),"0")

Named Ranges
Name	Refers To	Cells
LeftP1	='Game 1'!$J$3:$J$34	J5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I5:I34	Expression	=A5=1	text	NO

 

[cSciFiChick]

Sorry I am a little confused by your explanation. I might be able to help with this if I understood better.

So you are saying when E1 is under 140 the average calculation in row 35 should stop calculating? So right now your example does not match that scenario right? Can you provide an example that shows it as under 140?

"if the remaining score when it's player 2s turn is 145 his score wants to be counted towards his average" His score 'wants' to be counted?

 

[HullBorn]

So you are saying when E1 is under 140 the average calculation in row 35 should stop calculating?

Yes, for all players for the remainder of the game.

if the remaining score when it's player 2s turn is 145 his score wants to be counted towards his average

Yes

Darts Scorer v1.xlsx
	B	C	D	E	F	G	H	I	J	K
1				138
2	Team 1									1001
3	Throws	Player 1	Player 2	Player 3	Player 4	Player 5	Player 6	Round	1001
4
5	1	100	45	90	56	56	100	447	554
6	2	50	20	65	45	45	21	246	308
7	3	120	50					170	138
8	4
9	5
10	6
11	7
12	8
13	9
14	10
15	11
16	12
17	13
18	14
19	15
20	16
21	17
22	18
23	19
24	20
25	21
26	22
27	23
28	24
29	25
30	26
31	27
32	28
33	29
34	30
35	Averages	90.00	38.33	77.50	50.50	50.50	60.50	287.67
Game 1

Cell Formulas
Range	Formula
E1	E1	=IF(COUNT(J5:J34)>0,MIN(J5:J34),K2)
J3	J3	=K2
I5:I34	I5	=IF(COUNT(C5:H5)>0,SUM(C5:H5),"")
J5	J5	=IF(LEN(I5)>0,IF(I5>J3,J3,IF(AND($B$1="Double",(J3-I5)=1),J3,J3-I5)),"")
J6:J34	J6	=IF(LEN(I6)>0,IF(I6>J5,J5,IF(AND($B$1="Double",(J5-I6)=1),J5,J5-I6)),"")
C35:H35	C35	=IFERROR(AVERAGE(C2:C29)+C32,"0")
I35	I35	=IFERROR(AVERAGE(I2:I29),"0")

Named Ranges
Name	Refers To	Cells
LeftP1	='Game 1'!$J$3:$J$34	J5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I5:I34	Expression	=A5=1	text	NO

 

[cSciFiChick]

I am sorry I can not find a solution unless you average per row. So I added columns to do that. If you want to hide those columns if you want. The Average on row 35 will still update.

MR Excel Solutions.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2										1001
3		Team 1									Averages
4	Throws	Player 1	Player 2	Player 3	Player 4	Player 5	Player 6	Round Total	Remaing Game Points		PIayer 1	Player 2	Player 3	Player 4	Player 5	Player 6
5	1	100	45	90	56	56	100	447	554		100.0	45.0	90.0	56.0	56.0	100.0
6	2	50	20	65	45	45	21	246	308		75.0	32.5	77.5	50.5	50.5	60.5
7	3	120	50					170	138
8	4
9	5
10	6
11	7
12	8
13	9
14	10
15	11
16	12
17	13
18	14
19	15
20	16
21	17
22	18
23	19
24	20
25	21
26	22
27	23
28	24
29	25
30	26
31	27
32	28
33	29
34	30
35	Averages	75	32.5	77.5	50.5	50.5	60.5
Sheet2

Cell Formulas
Range	Formula
I5:I34	I5	=IF(COUNT(C5:H5)>0,SUM(C5:H5),"")
J5	J5	=IF(LEN(I5)>0,IF(I5>K2,K2,IF(AND($C$1="Double",(K2-I5)=1),K2,K2-I5)),"")
J6:J34	J6	=IF(LEN(I6)>0,IF(I6>J5,J5,IF(AND($C$1="Double",(J5-I6)=1),J5,J5-I6)),"")
L5:L34	L5	=IF(J5="","",IF($J5>140,AVERAGE(C$5:C5),""))
M5:M34	M5	=IF(J5="","",IF($J5>140,AVERAGE(D$5:D5),""))
N5:N34	N5	=IF(J5="","",IF($J5>140,AVERAGE(E$5:E5),""))
O5:O34	O5	=IF(J5="","",IF($J5>140,AVERAGE(F$5:F5),""))
P5:P34	P5	=IF(J5="","",IF($J5>140,AVERAGE(G$5:G5),""))
Q5:Q34	Q5	=IF(J5="","",IF($J5>140,AVERAGE(H$5:H5),""))
C35:H35	C35	=LOOKUP(2,1/(L:L<>""),L:L)

 

[HullBorn]

I am sorry I can not find a solution unless you average per row. So I added columns to do that. If you want to hide those columns if you want. The Average on row 35 will still update.

MR Excel Solutions.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2										1001
3		Team 1									Averages
4	Throws	Player 1	Player 2	Player 3	Player 4	Player 5	Player 6	Round Total	Remaing Game Points		PIayer 1	Player 2	Player 3	Player 4	Player 5	Player 6
5	1	100	45	90	56	56	100	447	554		100.0	45.0	90.0	56.0	56.0	100.0
6	2	50	20	65	45	45	21	246	308		75.0	32.5	77.5	50.5	50.5	60.5
7	3	120	50					170	138
8	4
9	5
10	6
11	7
12	8
13	9
14	10
15	11
16	12
17	13
18	14
19	15
20	16
21	17
22	18
23	19
24	20
25	21
26	22
27	23
28	24
29	25
30	26
31	27
32	28
33	29
34	30
35	Averages	75	32.5	77.5	50.5	50.5	60.5
Sheet2

Cell Formulas
Range	Formula
I5:I34	I5	=IF(COUNT(C5:H5)>0,SUM(C5:H5),"")
J5	J5	=IF(LEN(I5)>0,IF(I5>K2,K2,IF(AND($C$1="Double",(K2-I5)=1),K2,K2-I5)),"")
J6:J34	J6	=IF(LEN(I6)>0,IF(I6>J5,J5,IF(AND($C$1="Double",(J5-I6)=1),J5,J5-I6)),"")
L5:L34	L5	=IF(J5="","",IF($J5>140,AVERAGE(C$5:C5),""))
M5:M34	M5	=IF(J5="","",IF($J5>140,AVERAGE(D$5:D5),""))
N5:N34	N5	=IF(J5="","",IF($J5>140,AVERAGE(E$5:E5),""))
O5:O34	O5	=IF(J5="","",IF($J5>140,AVERAGE(F$5:F5),""))
P5:P34	P5	=IF(J5="","",IF($J5>140,AVERAGE(G$5:G5),""))
Q5:Q34	Q5	=IF(J5="","",IF($J5>140,AVERAGE(H$5:H5),""))
C35:H35	C35	=LOOKUP(2,1/(L:L<>""),L:L)

 

[HullBorn]

Thanks, that's a good solution.

Is there a way I can download the spreadsheet?

Thanks again.