average last 30 non zero entries in a column, update automatically

aaacccc

New Member
Joined
Jul 11, 2013
Messages
19
I have several columns and at the end of the column I need to average the last 30 non zero values. So far I have this:

=SUM(OFFSET(D16,COUNTA(D16:D326)-29,0,30,1))
column starts at D16 and ends D326

it seems to be working, but it automatically selects the last 30 entries regardless of the value, there are many cells that are empty and so I made them zero. I want an average of the last 30 values that are not zero. I will be adding rows to the column and so I want the formula to update as I add more entries.

I do not know much about excel, and keep reading online about arrays and codes, the simplest way would be best, thanks!
ex:

D
.
.
.
16
.
.
.
326
Average here
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Try:

Code:
=AVERAGE(IF(ROW(A1:A100)>=LARGE(IF(A1:A100,ROW(A1:A100)),30),IF(A1:A100,A1:A100)))

Committed with CTRL+SHIFT+ENTER.

Matty
 
Upvote 0
matty, thank you for your help, do I put the code in the cell or does it go elsewhere and when do I enter the CTRL+SHIFT+ENTER, I am really not very familiar with excel
 
Upvote 0
Yes, the formula goes in the cell where you want the result to be returned. It should reference the range containing your data (i.e. change A1:A100 to where your data actually resides).

As the formula uses IF within an array, it needs to be committed with CTRL+SHIFT+ENTER to work. If done correctly, curly brackets {} will surround the formula when seen in the formula bar.

Matty
 
Upvote 0
I have =SUMIF(B1:B150,">0")/COUNTIF(B1:B150,">0") which I am using at the end of the column so that it gives me an everage of only the non-zero values and I came up with =AVERAGE(OFFSET(B1,COUNTA(B1:B150)-31,0,30,1)) which gives me the sum of the last set of 30 values, but I cannot figure out how to get the average for the last 30 non-zero entries
 
Upvote 0
I have =SUMIF(B1:B150,">0")/COUNTIF(B1:B150,">0") which I am using at the end of the column so that it gives me an everage of only the non-zero values and I came up with =AVERAGE(OFFSET(B1,COUNTA(B1:B150)-31,0,30,1)) which gives me the sum of the last set of 30 values, but I cannot figure out how to get the average for the last 30 non-zero entries

The formula I provided will work, as long as you commit it with CTRL+SHIFT+ENTER. The error you're getting is because you have only committed it with ENTER.

Matty
 
Upvote 0
I guess initially, I wanted the average of the last 30 non zero values, but what I actually need is the average of the non zero values within the last 30 entries only. How does that change the formula that I have already?
 
Upvote 0
I guess initially, I wanted the average of the last 30 non zero values, but what I actually need is the average of the non zero values within the last 30 entries only. How does that change the formula that I have already?

Sounds like:

Code:
=AVERAGEIF(B71:B100,">0",B71:B100)

Matty
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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