Best Bowling Figures

Hi, if anyone can help me figure out how to do Best Bowling figures for my spreadsheet it'd be awesome. The file is attached. The inputs are in the Inputs! sheet (surprise, surprise) and I would like the Best Bowling figures to be on the Stats! page. Thanks

I would refer to an earlier thread:

http://www.excelforum.com/excel-2007...e-columns.html

Using your example:

Stats sheet

P16:
=MAX(IF(Inputs!$B$2:$B$387=$A16,Inputs!$Z$2:$Z$387))
confirmed with CTRL + SHIFT + ENTER (enter alone will not suffice)
copied down to P27
 
Then
 
O16:
=IF(MOD(P16,1),INT(P16)&"-"&ROUND((1-MOD(P16,1))*1000,0),"n/a")
confirmed with Enter copied down to O27

The reason we use P is to avoid performing the array twice over.

Other suggestions:

Stats sheet

B16:
=SUMIF(Inputs!$B:$B,$A16,Inputs!$C:$C)
copied down to B27
 
C16:
=SUMIF(Inputs!$B:$B,$A16,Inputs!P:P)
copied across matrix C16:G27
 
K16:
=SUMIF(Inputs!$B:$B,$A16,Inputs!V:V)
copied across matrix K16:N27

Originally Posted by kasablur

...I tried the best bowling formulas (and cnahged the Z's to U's) and it cam eup with n/a for each player. Have I done something wrong?

The best bowling formula on Stats sheet utilised the BB formula on Inputs sheet. In your most recent version you have adapted your Inputs sheet and removed the BB formula (previously column Z) - hence the n/a returns.

Adapt your latest version as follows:

Inputs
AA1:
BB

AA2:
=IF(Q2=0,"",U2+(1-MAX((0&T2)+0,0.1)/1000))
copied down to AA387

Then

Stats
P16:
=MAX(IF(Inputs!$B$2:$B$387=$A16,Inputs!$AA$2:$AA$387))
confirmed with CTRL + SHIFT + ENTER (Enter alone will not suffice)
copied down to P27
If the array is set correctly the above will appear encased within { } [these can not be typed]

If the above have been added correctly the Stats results will be as follows:

2-10
3-31
1-10
0-15
0-40
0-31
n/a
5-27
0-8
0-19
1-1
0-0

Unfortunately I don't have a lot of time on the board thesedays so I've reviewed your attachment and provided a streamlined version of your file covering batting/bowling - hopefully from this you can deduce the remainder - if not post back and I will revist when able (unless resolved by others in the meantime).