Formulae for golf syndicate problems x3 below, can anyone help?

[Paul R Bentham]

Hi,
I've recently taken over a golf syndicate secretary post and have inherited a manual admin system for keeping the weekly scores and updating league positions etc and thought this would be more easily acheived via Excel and I have 3 immediate issues which I'd like your help with if possible:

1. I need a formula that will give me the group average score for the week. Not every player plays each week so the number of scores entered into the row for that week vary so I need a formula that totals the cells with values and give the average for only those cells?

2. This is the same as above but by columns to give individual players average as the year progresses. (Hope that makes sense?)

3. and finally, I need a formula to pick the each players best 10 scores from the year and display them in a second worksheet; here's the rub...I need each of the ten to be picked throughout the year as each week progresses through the year and also to enter each of the ten best into its own cell?

HELP!

If anyone can assist I'd be very grateful.

Thank, Paul B.

[L-Drr]

1. use the AVERAGE function. this does not get messed up by empty values.
2. I don't understand
3. use the LARGE function

If you need more help, please upload a workbook so we can understand

[arlu1201]

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Paul R Bentham]

Hi,
Have attached the workbook; 2 worksheets with comments in the cells where the formulas are required, hope this helps and thanks for coming back to me both.

Paul B.

[Paul R Bentham]

Hi, I've fixed (i think) the average formulas, Thanks L-Drr but the last formula I'm not sure how the large function works and how to get each of the playes top 10 scores into the individual cells on the 2nd worksheet?

Thanks.

[Fotis1991]

Perhaps in B3 and copy down and across this ARRAY formula.

=LARGE(IF('Weekly scores 2013'!$C$1:$V$1=$A3,'Weekly scores 2013'!$C$4:$V$55),COLUMN(A1))

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[Paul R Bentham]

Agreat help and the "LARGE" formula works a trick (Not sure how?? as it doesn't make sense to me:-)) but a great help.

Thnk you so much.
kind regards,

Paul B.

[L-Drr]

In the LARGE function, the first argument selects the range from which the biggest numers should be taken ('Weekly scores 2013'!$C$4:$V$55). The second argument sais if it should take the very largest number, of the second largest, or the third largest etc. To not have to fill that second argument manually (1, 2, 3, 4), Fotis used COLUMN(A1), which results in 1 (column number of cell A1). If you copy the formula one cell to the right, it changes to COLUMN(B1) (like all formulas do when you copy them), which results in the column number 2.

[Paul R Bentham]

Hi L-Drr,

Thanks for the explanation, although I can see the logic it still doesn't make a lot of sense to me as the last cell reference is or seems to be random!??? But again;

A BIG THANK YOU for all the help, you are truly a STAR!

Kind regards,

Paul B.

[L-Drr]

Yes it is random
It could just as well be A3 or A1000, only thing that matters is that it gives back the number 1 (column number of A), and it automatically changes to number 2 when you copy it one celll to the right