Average last 3 non-contiguous, non-zero values

**hermansnerd** · 07-15-2015, 11:24 AM

I have a sheet where i need to average values of 3 non-contiguous cells (actually creating handicaps for a golf league at work). That in and of itself isn't hard. Where the problem comes in--and where I can't seem to wrap my head around this--is that if the value is 0 then I need to move on to the next non-zero week and use that. For example, if my scores for the first 6 weeks are 74, 0, 0, 70, 59, and 77 and I want to get the average for week 6 then I need to average b10, b4, and b2. The kicker is that I can only use a maximum of 3 scores. I can't figure out how to do ths for the life of me... The layout for the values is this:

Please Login or Register  to view this content.

the final formula should look like:

Please Login or Register  to view this content.

**sourabhg98** · 07-15-2015, 11:28 AM

Seems to be confusing...
Could you attach a sample file with results manually mocked up??
You can attach a sample workbook by clicking on "Go Advanced" button at the foot of the typing area and then clicking on the "Paper Clip" icon from the tools above...

**hermansnerd** · 07-15-2015, 11:50 AM

Here's the example. Each score is rounded to the nearest whole number.

scores_example.xlsx

**sourabhg98** · 07-15-2015, 12:23 PM

What should be the average for week 6 in this example?

**newdoverman** · 07-15-2015, 12:54 PM

If you remove the text row 12, then these ARRAY formulae will work:

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

**hermansnerd** · 07-15-2015, 01:04 PM

the average for week 6 should be 30

**hermansnerd** · 07-15-2015, 01:11 PM

This seems to give me an overall average...i need an average for each week...unless i'm missing something.

**newdoverman** · 07-15-2015, 01:38 PM

What average do you want? You seem to be asking for two very different averages. I have given you the average for the last 3 values >0 for all the weeks.

**hermansnerd** · 07-15-2015, 01:48 PM

Sorry if i was a bit confusing in my description. I need to generate a handicap for each week that is generated off the three most recent non-zero scores. So, for instance, week 6 the handicap would use week 6, week 5, and week 1 (because weeks 2, 3, and 4 are all 0). I hope this clears it up a bit.

**newdoverman** · 07-15-2015, 03:26 PM

Here is a workbook that uses 3 helper columns to calculate the 3 scores to count: (Columns H, I, J) and the average in Column K
Enter this formula in H4 and fill across and down:
Array formula entered with Ctrl + Shift + Enter

Formula:

Please Login or Register  to view this content.

Enter in K4 and fill down:

Formula:

Please Login or Register  to view this content.

**hermansnerd** · 07-15-2015, 03:46 PM

getting closer...the previous three scores need to be from the previous weeks...not just the previous 3 rows...so, to use my example from above, to calculate the handicap for week 6 (C30) we would average (B15(77), B4(70), B2(74)) not just the previous three non-zero rows (b15, b7, b5). sorry this is such a cluster, but this is why i came here...my feeble mind can't seem to figure out how to make this work

and i really do appreciate any/all the help i can get.

**newdoverman** · 07-15-2015, 04:54 PM

If you look closely, you will see that the scores are the last 3 scores and not just the last 3 rows.

**newdoverman** · 07-15-2015, 06:49 PM

I see what you are doing. To calculate the 3 previous weeks without zero values, combine the two sets (front and back 9) as it doesn't make sense calculation-wise to have them separated. You can like I show in this example have the two sorted together and the calculations that I have already given you will calculate the data correctly averaging the last 3 weeks > 0

	A	B	C	D	E	F	G	H
1	Week	Score	HC	Course
2	1	74	30	Front 9
3	2	0	30	Back 9	Last Score	Second Last	Third Last	3 Score Ave
4	3	0	30	Front 9	74
5	4	0	30	Back 9	74
6	5	70	29	Front 9	70	74
7	6	77	30	Back 9	77	70	74	73.66667
8	7	59	26	Front 9	59	77	70	68.66667
9	8	57	23	Back 9	57	59	77	64.33333
10	9	0	23	Front 9	57	59	77	64.33333
11	10	0	23	Back 9	57	59	77	64.33333
12	11	48	15	Front 9	48	57	59	54.66667
13				Back 9
14				Front 9
15				Back 9
16				Front 9
17				Back 9
18				Front 9
19				Back 9

**newdoverman** · 07-15-2015, 07:52 PM

I have created a second worksheet and moved the calculations there. (same calculations as above) This leaves your original way of data entry intact and does the calculations on the second worksheet.

**hermansnerd** · 07-16-2015, 02:14 PM

Awesome! Had to massage it a bit to fit the final use case but i got it working. Thanks a ton for all the help. I really appreciate it.

**newdoverman** · 07-16-2015, 02:52 PM

Thanks for the feedback.

Average last 3 non-contiguous, non-zero values

LinkBack

Thread Tools

Rate This Thread

Display

Average last 3 non-contiguous, non-zero values

Re: Average last 3 non-contiguous, non-zero values

Re: Average last 3 non-contiguous, non-zero values

Re: Average last 3 non-contiguous, non-zero values

Re: Average last 3 non-contiguous, non-zero values

Re: Average last 3 non-contiguous, non-zero values

Re: Average last 3 non-contiguous, non-zero values

Re: Average last 3 non-contiguous, non-zero values

Re: Average last 3 non-contiguous, non-zero values

Re: Average last 3 non-contiguous, non-zero values

Re: Average last 3 non-contiguous, non-zero values

Re: Average last 3 non-contiguous, non-zero values

Re: Average last 3 non-contiguous, non-zero values

Re: Average last 3 non-contiguous, non-zero values

Re: Average last 3 non-contiguous, non-zero values

Re: Average last 3 non-contiguous, non-zero values

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Using scripting dictionary to add contiguous group of items from non contiguous range

calculate average of non contiguous row

average formula for non contiguous range and eliminate blank cells

[SOLVED] How to delete multiple contiguous (and/or) non-contiguous rows, in an excel table

Average of non-contiguous column

Running Average with non-contiguous data

Average Non-Contiguous numbers

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions