Average of last two values that are not 0

[buckmaster32]

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)

<tbody>
</tbody>

Any help would be appreciated.

<tbody></tbody>

 

[buckmaster32]

Anyone? Any ideas?

 

[yearnintolearn]

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.

 

[buckmaster32]

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

<tbody>
</tbody>

 

[AhoyNC]

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)

Excel Workbook
	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
Sheet