Maxa returning #DIV/0!

[Alan Gardner]

I have a large spreadsheet for a bowling league which calculates scores individual & team each team in rows. One column calculates the players average and I need to have the teams highest average scorer calculate into a extra cell for ease of identity. The maxa function works well when scores have been entered but until all the players in a team have played it only comes up with #DIV/0!. I can have up to twenty players for the team of 8 so it doesn't work.

#DIV/0!
#DIV/0!
#DIV/0!
#DIV/0!
---------
#DIV/0!
--------
in anticipation
regards Alan

[alansidman]

Would be helpful if you posted a spreadsheet with some sample data. Click on Go Advanced and follow wizard to upload.

[JSallen]

Please Login or Register  to view this content.

Will give you the largest number in column A, ignoring blanks and text. Would that solve your problem?

[Alan Gardner]

yes It would be easier with the spreadsheet
hope its ok

[Alan Gardner]

sorry chaps new to this uploaded wrong thing altogether previously
veterans averages.xls

[FDibbins]

exactly where is this errant formula of yours?

[Alan Gardner]

thanks FDibbins I was attempting to show DIV 1 sheet,column AS, rows 5 to 21. Tryin to get the maximum average into cell AS23 I have not yet entered any scores but but when I put in trial scores it only comes up with #Div/0! if i fill in all players to have a score it will work
again sorry for confusions but at my age its awonder I can do any at all
Regards Alan

[FDibbins]

this thread is marked as solved, is that correct?

[Alan Gardner]

No it is not solved, I was all over the place yesterday with family interruptions so did attempt to redo it all with better info but this appears to be against the forum rules.

[Alan Gardner]

Having made a complete mess of my first post I will try again
I have a bowling league with 70 teams in 7 divisions
The spreadsheet for each team/individual member collates their stats and one which takes time is the full player average as we have to issue league tables and each teams highest individual leader regularly.

I have used the maxa and Large function which both work if every player gets a game early but some teams have up to 20 players registered for an 8 man team and most do not get picked early. so the functions do not work because we are / 0 in some cases and only #div/0! will appear.

I attach a better spreadsheet this time and it is the column AS cells 23&47 where the examples are

thanks for your patience

Alan

[Pepe Le Mokko]

Perhaps in AS23 :

Please Login or Register  to view this content.

entered as an arry formula with Ctrl+Shift+Enter

EDIT for the CF use

Please Login or Register  to view this content.

entered after first selecting range AS5:AS21
( perhaps adapt range as needed)

[Marcol]

I'm not clear as to what you are trying to do in you "totals" Row(22:22), where does BB22 come from?
I've tried a few guesses at alternative formulae, but that's all they are.
In Sheet1 (2) I have left some of the row empty until you clarify your objectives.

The main objective of this workbook is to try to simplify your initial fourmulae to make the sheet more readable and eliminate Excel error values at source.

Sheet1 is your layout as it stands, Sheet1 (2) has extra columns with what might be useful information, and also make some other formula easier to understand.

Give it a try and see if it does most of what you need, and we can take it from there.

[Alan Gardner]

Thank you Marcol it works very well, in rows 22:22 I am copying the scorecard sent by teams to firstly add stats and they are copied to a weekly result sheet (the scores 168-120 say) BB22 is in case of a draw 168-168 in which case i mark it in these are all auto to the league table sheet. couldn't find a formula to give a draw result
Again thanks for your patience and replies
Alan