Attend Excelapalooza
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Average of last two values that are not 0

  1. #1
    New Member
    Join Date
    Mar 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Average of last two values that are not 0

    Basically I have a 14 week league that I am recording two scores a week for. I'm having a hard time calculating the handicap. This should be the 80% of the average of the sum of score 1 and score 2 for the last 2 weeks there were scores entered, subtracted from 360. If there is scores entered each week there is no problem. Issue i'm having is if a week is missed the handicap is blown way off as it treats the missed week as a 0. I need it to look for the last 2 weeks there was a score and do the calculations on those numbers.

    Here is the formula I am using for the current handicap so you get a better understanding what I'm looking for.

    =(360-AVERAGE(SUM(BI3:BI4),SUM(BE3:BE4)))*0.8

    Below is a table explaining what i'm looking for. the handicap row shows what the results should be.


    Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7
    Score 1 250 300 280 300 300
    Score 2 40 50 50 45 40
    Handicap Look just at 1(56) look to 1&2 (32 hand) look to 1&2 (32 hand) look at 2&4 (16) look at 4&5 (18) look at 4&5 (18)


    Any help would be appreciated.

  2. #2
    New Member
    Join Date
    Mar 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average of last two values that are not 0

    Anyone? Any ideas?

  3. #3
    New Member
    Join Date
    Nov 2013
    Posts
    43
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average of last two values that are not 0

    I'm not readily available to put a formula into excel however, I believe tthat you will need to use a combo of column (to encapsulate most recent weeks), if(weed out 0's or ""), and large(to give precedence to the weeks that are most early) all entered as an array formula. If your unsure of how to bring this all together, respond with the columns for this data and I can get a formula to you tomorrow.

  4. #4
    New Member
    Join Date
    Mar 2015
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Average of last two values that are not 0

    I simplified the request but am not familiar with the offset formula. Here is an example. I'm need to sum up the last 2 cells that contain a number in the bottom row. So for this example it would be 342 and 331. From right to left. The Game Score cell is in B2 and the Range is B to R. I need to populate the data in the cell above the 342 cell so that would be R17. If there isn't two values return 0.


    Game Score 98 97 100 295 99 98 97 294 98 97 99 294 100 100 100 300
    Hit's/X's 15 16 11 42 12 15 11 38 11 15 11 37 15 15 12 42
    Handicap/Game
    337 332 331 342
    Last edited by buckmaster32; Mar 15th, 2015 at 10:49 PM.

  5. #5
    Board Regular
    Join Date
    Oct 2011
    Posts
    3,673
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Average of last two values that are not 0

    Maybe you can adapt this to your needs.
    I made some changes to the formula in this link on getting last 3 golf scores.
    Daily Dose of Excel Blog Archive Sum Last Three Golf Scores

    This is an array formula and must be entered with CTRL-SHIFT-ENTER (not just enter)

     ABCDEFGHIJKLMNOPQ
    11. Score9897100295999897294989799294100100100300
    2Hit's/X's15161142121511381115113715151242
    3Handicap/Game                
    4    337   332   331   342
    5                 
    6                673

    Spreadsheet Formulas
    CellFormula
    Q6{=IFERROR(SUM(Q4:INDEX(B4:Q4,LARGE(COLUMN(B4:Q4)*(B4:Q4<>"")-COLUMN(B4)+1,2))),"")}
    Formula Array:
    Produce enclosing
    { } by entering
    formula with CTRL+SHIFT+ENTER!


    Excel tables to the web >> Excel Jeanie HTML 4

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

DMCA.com