Results 1 to 9 of 9

Thread: Sum last 10 entries in a row

  1. #1

    Sum last 10 entries in a row



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Hi,
    Can you post your datas in attachment ?
    Eric

  3. #3
    Hopefully you can see the file I attached?

    Quote Originally Posted by terryod View Post
    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.

  4. #4
    Acolyte Sixthsense's Avatar
    Join Date
    Nov 2012
    Location
    India
    Posts
    26
    Articles
    0
    @ 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

  5. #5
    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.

    Quote Originally Posted by terryod View Post
    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.

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

  7. #7
    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?

  8. #8
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    675
    Articles
    0
    Excel Version
    Excel 2010 64bit
    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.

  9. #9
    Acolyte Sixthsense's Avatar
    Join Date
    Nov 2012
    Location
    India
    Posts
    26
    Articles
    0
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •