Sum last 10 entries in a row

terryod

New member
Joined
Feb 28, 2014
Messages
4
Reaction score
0
Points
0
Hi
I'm a novice excel user. I need to make a spreadsheet for clay target club. I want to add the last 10 entries in a row to give the person a handicap. Can anyone help? Can't be the last 10 rows, because they don't all shoot every week. Must be the last 10 entries.
 

terryod

New member
Joined
Feb 28, 2014
Messages
4
Reaction score
0
Points
0
Hopefully you can see the file I attached?

Hi
I'm a novice excel user. I need to make a spreadsheet for clay target club. I want to add the last 10 entries in a row to give the person a handicap. Can anyone help? Can't be the last 10 rows, because they don't all shoot every week. Must be the last 10 entries.
 

Attachments

  • Copy - broome field and game score sheet current version 2013.xlsx
    30.2 KB · Views: 37

Sixthsense

New member
Joined
Nov 6, 2012
Messages
26
Reaction score
0
Points
0
Location
India
@ terryod,

Please give some description about your data and with cell/Range/Sheet references and expected results. So that we can able to get better understanding of your expectation :)
 

terryod

New member
Joined
Feb 28, 2014
Messages
4
Reaction score
0
Points
0
In column A there is a list of participants. Their scores are listed to the right under a column header for the date of the shoot. We just add an extra column for each new week. I just want to add the total of the last 10 scores, to give a handicap.

Hi
I'm a novice excel user. I need to make a spreadsheet for clay target club. I want to add the last 10 entries in a row to give the person a handicap. Can anyone help? Can't be the last 10 rows, because they don't all shoot every week. Must be the last 10 entries.
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
Typed your thread title into Google and found similar type question asked on another forum in 2006.

Because your dates/scores are columns H thru IW, the formula I can see (without an account there) adapts to something like this

=SUM(IF(COLUMN(H2:IW2)>=LARGE(IF(H2:IW2>0,COLUMN(H2:IW2)),10),H2:IW2)) confirmed with Control+Shift+Enter.
 

terryod

New member
Joined
Feb 28, 2014
Messages
4
Reaction score
0
Points
0
Thanks NoS the above formula works for all participants with 10 shots or more recorded to their name - awesome :)

Can I add an "If" and put the formula above in the the positive, to avoid the #NUM! result for those with less than 10 shots?

I did try, but couldn't make it work? Think it's got to do with the ctrl+shift+enter?
 

NoS

New member
Joined
Jan 17, 2013
Messages
832
Reaction score
0
Points
0
Location
British Columbia
Excel Version(s)
Excel 2010
Hi terryod

Hopefully some of the "Formula Wizards" will chime in with a solution.

I just did a quick Google search for what I thought an interesting question and indicated what I found.

What result would you want for a participant with less than 10 shots?
Is the formula you're after to go in column E?
Do you really need to go to column XDF for your calculations? At 2 weeks per column, the date in that column would be over 600 years from now.

My tendency for this would be a macro with a couple of loops but I'm sure a formula using Excel's built in functions would be better and faster, so, like you I'm waiting to see what others will suggest.
 

Sixthsense

New member
Joined
Nov 6, 2012
Messages
26
Reaction score
0
Points
0
Location
India
In E2 Cell - Array Formula - Requires CTRL+SHIFT+ENTER

=SUM(INDEX(H2:IW2,,MATCH(MIN(IFERROR(LARGE(IF(H2:IW2<>"",COLUMN(H2:IW2)),ROW($1:$10)),9^9)),COLUMN(H2:IW2),0)):IW2)

Drag it down...

Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.
 
Top