Thanks:  0
Likes:  0

# Thread: Average of last two values that are not 0

1. ## 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. ## Re: Average of last two values that are not 0

Anyone? Any ideas?

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

5. ## Re: Average of last two values that are not 0

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)

 A B C D E F G H I J K L M N O P Q 1 1. Score 98 97 100 295 99 98 97 294 98 97 99 294 100 100 100 300 2 Hit's/X's 15 16 11 42 12 15 11 38 11 15 11 37 15 15 12 42 3 Handicap/Game 4 337 332 331 342 5 6 673

 Cell Formula 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

#### Posting Permissions

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