formulas help needed

[adbarnes81]

I'm trying to calculate the average of B2:B12, minus any cells that do not equal 100 or more. For example:

34 (does not meet requirements for this formula)
114
156
158
153
160
132
152
153
159
136

What formula can I enter into the specific cell that would return the average of 147.3?
I know how to use =average(B2:B12), but I'd like it to not include B2, some cases may have a different cell to be excluded.

I would like this formula to encompass the enter cell chain so that I can enter new stats and it will recompute the average as long as they meet the one requirement.

 

[BabyTiger]

Hi there,

You can try the following formula:

=SUMIF(B2:B12,">100")/COUNTIF(B2:B12,">100")

Hope it helps.

 

[sanrv1f]

may be this

Excel Workbook
	A	B	C
1	34		147.3
2	114
3	156
4	158
5	153
6	160
7	132
8	152
9	153
10	159
11	136
Sheet1

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[adbarnes81]

Hi there,

You can try the following formula:

=SUMIF(B2:B12,">100")/COUNTIF(B2:B12,">100")

Hope it helps.

that works perfectly, thank you.

now that takes care of games per year if games played >100

how would i get an average At Bats/Game working with the similar criteria of games played per season

Player A has played 11 season with the number of games played list as B2:B12. His at bats per season are C2:C12.
I can simply use =sum(C2:C12)/sum(B2:B12) but that doesnt take out any games or at bats played in a <100 game season

 

[BabyTiger]

You can try this:

=SUMIF(B2:C12,">100",C2:C12)/(SUMIF(B2:B12,">100")/COUNTIF(B2:B12,">100"))

Hope I've understood your query. Sankar may have a much simpler array formula.

 

[sanrv1f]

may be this

Excel Workbook
	B	C	D	E
1	Game	Bats
2	34	14		0.077473
3	114	4
4	156	9
5	158	9
6	153	3
7	160	18
8	132	19
9	152	12
10	153	10
11	159	15
12	136	15
Sheet1

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

 

[adbarnes81]

You can try this:

=SUMIF(B2:C12,">100",C2:C12)/(SUMIF(B2:B12,">100")/COUNTIF(B2:B12,">100"))

Hope I've understood your query. Sankar may have a much simpler array formula.

I tried that and i don't think its quite right
thank you though

 

[BabyTiger]

Sankar's suggestion should work perfectly.

Just wanting to update the formula I've provided, it should be:
=SUMIF(B2:C12,">100",C2:C12)/SUMIF(B2:B12,">100")

Without the CountIf bit.

 

[adbarnes81]

may be this

Excel Workbook
	B	C	D	E
1	Game	Bats
2	34	14		0.077473
3	114	4
4	156	9
5	158	9
6	153	3
7	160	18
8	132	19
9	152	12
10	153	10
11	159	15
12	136	15
Sheet1

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter these manually yourself

I tried that but it showed #value!

ideally i'd like it to exclude anything in B2:B12 that doesn't meet the >100. and to also exclude the corresponding C2:C12, then take the average C2:C12/B2:B12

i hope that helps explain it more

 

[adbarnes81]

Sankar's suggestion should work perfectly.

Just wanting to update the formula I've provided, it should be:
=SUMIF(B2:C12,">100",C2:C12)/SUMIF(B2:B12,">100")

Without the CountIf bit.

I made a tweak to your formula, it works great if you use:
=SUMIF(B2:B12,">100",C2:C12)/SUMIF(B2:B12,">100")


thanks a bunch guys