Complex Averaging Question

E

EXCELrulez

New Member
Joined
Dec 17, 2014
Messages
6
Hello. I am hoping someone can help answer my question. I am working on a spreadsheet that will average 30 numbers, excluding zeros. These numbers are in a column and do include zeros. Below is the formula I have created, but it falls short.

=IF((SUM(F194:F208)=0)," N/A ", (AVERAGEIF((OFFSET(F208,0,0,(SUM(-30-(COUNTIF((OFFSET(F208,0,0,-30)),"=0")))))),">0")))

It will go back 30 rows, count the zeros, and then go back more rows for however many zeros it counted. It will then exclude the zeros from the average. The problem, however, there is no guaranteed it is averaging exactly 30 numbers. When it counts the zeros and extends the range, it picks up more zeros, resulting in an average less than 30 numbers. Would anyone have a suggestion on a formula that would guaranteed an average of 30 numbers? I am willing to look into VBA, if there are any suggestions.

Thanks for your help.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
If I were starting on cell G209, I would need it to go back 30 rows. It would start at F208 and go back giving me the first 30 non-zero numbers.
 
Upvote 0
EXCELrulez said:
I am working on a spreadsheet that will average 30 numbers, excluding zeros. These numbers are in a column and do include zeros.
[....]
It will go back 30 rows, count the zeros, and then go back more rows for however many zeros it counted. [....] I am willing to look into VBA
Click to expand...

A VBA function is straight-forward. Usage:

=IF(SUM(F194:F208)=0," N/A ",avgLastN(F1:F208,30))
or
=IFERROR(avgLastN(F1:F208,30)," N/A ")

(Not sure why you have the SUM(F194:F208)=0 condition.)

Code: 
Function avgLastN(r As Range, n As Long)
Dim i As Long, nCnt As Long
ReDim v(1 To n) As Double
avgLastN = CVErr(xlErrNA)
For i = r.Count To 1 Step -1
    If WorksheetFunction.IsNumber(r(i)) Then
        If r(i) <> 0 Then
            nCnt = nCnt + 1
            v(nCnt) = r(i)
            If nCnt = n Then
                avgLastN = WorksheetFunction.Average(v)
                Exit Function
            End If
        End If
    End If
Next
End Function
 
Last edited:
Upvote 0
Aladin Akyurek said:
What is the range you are dealing with?
Click to expand...

The range is what I am having difficulty with. This will be in a column with 200 numbers, alternating with zero and non-zero numbers. I will need it to only select the first 30 non-zero numbers and average them. I think it needs the range to expand and contrast as needed.

Thank you for your help.
 
Upvote 0
joeu2004 said:
(Not sure why you have the SUM(F194:F208)=0 condition.)
Click to expand...

I had that to simply keep there from being a huge column of zeros for numbers it had not averaged yet, it isn't necessary. I will try your vba code tomorrow, when I get back to the spreadsheet.

Thank you for your help.
 
Upvote 0
EXCELrulez said:
This will be in a column with 200 numbers [...]. I will need it to only select the first [sic!] 30 non-zero numbers and average them.
Click to expand...

Make up your mind!

You said last 30 non-zero numbers in other postings in this thread, to wit: "OFFSET(F208,0,0,-30)", albeit incorrect syntax; "go back 30 rows, count the zeros, and then go back more rows"; "starting on cell G209, I would need it to go back 30 row".

Granted, you did write: "start at F208 and go back giving me the first [sic!] 30 non-zero numbers". But in that context ("go back ..."), "first 30" could only be interpreted as "first 30 as we go backwards". Klunk!

EXCELrulez said:
I think it needs the range to expand and contrast as needed.
Click to expand...

The VBA code that I provided assumes you provide the range. Of course, you can "expand and contract" the range when you enter it.

And with some care, Excel will expand and contract the range automatically as you insert or delete rows.

If you would prefer, the VBA code can determine the start and/or end of the data if you tell us how the data is delimited. For example, perhaps the data starts with the first numeric value in a column and ends with the last numeric value in the same column.

Just specify the rules clearly for determining the start and end of the data.

Also, be clear on whether you want to average the first 30 non-zero values ("first" means from the top) or last 30 non-zero values ("last" means from the bottom).
 
Upvote 0
Sorry for the confusion. Let's say I want the answer in cell F201. The numbers to be averaged will be the last 30 non-zero numbers. Starting at F200 and ending at whatever cell happened to be at the end of the 30 non-zero numbers. I won't be able to add or remove rows, so that won't be an issue. I just need a way for F201 to give me an average of the last 30 non-zero numbers. Because of the zeros in the data, I'm unable to specify an end cell for the average. This is where my problem is. Again sorry for the confusion, it's been a long day. Thanks for the help.
 
Upvote 0
Aladin Akyurek said:
Are you wanting the first 30 non-zero figures ot the last 30 non-zero figures?
Click to expand...

Aladin Akyurek said:
What is the range you are dealing with?
Click to expand...

EXCELrulez said:
Sorry for the confusion. Let's say I want the answer in cell F201. The numbers to be averaged will be the last 30 non-zero numbers. Starting at F200 and ending at whatever cell happened to be at the end of the 30 non-zero numbers. I won't be able to add or remove rows, so that won't be an issue. I just need a way for F201 to give me an average of the last 30 non-zero numbers. Because of the zeros in the data, I'm unable to specify an end cell for the average. This is where my problem is. Again sorry for the confusion, it's been a long day. Thanks for the help.
Click to expand...

Too much preoccupied with those zero values...

F201 is the formula cell.

The figures of interest are above F201.

Control+shift+enter, not just enter:
Rich (BB code): 
=AVERAGE(IF(ROW(F1:F200)>=LARGE(IF(ISNUMBER(1/F1:F200),ROW(F1:F200)),
  MIN(30,INDEX(FREQUENCY(F1:F200,0),2))),IF(F1:F200>0,F1:F200)))
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
How to get average of percentages in a column with filtered cells and excluding zero
Replies
2
Views
169
Fluff
Fluff
K
  • Solved
Excel offset average help
Replies
8
Views
351
Peter_SSs
Peter_SSs
T
  • Solved
Challenge Question: A Quest for Elegance
Replies
6
Views
253
Peter_SSs
Peter_SSs
E
  • Question
average or sumproduct?
Replies
0
Views
281
excelnewbie26
E
Sunline
  • Question
Find exact match, and the closest smallest and highest numbers.
Replies
3
Views
350
Sunline
Sunline

Forum statistics

Threads
1,214,516
Messages
6,119,979
Members
448,934
Latest member
audette89

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top