Average of last 12 nonblank values in row
Hi,
I am in need of assistance!
I am trying to create a sheet which averages the last 12 numbers in a row.
The problems I am running into are:
-Sometimes there are fewer than 12 numbers in a row
-There are blank cells before, inbetween and after the ranges
(If there are fewer than 12 entries, I just need the average of whatever is there)
Many thanks
Attached is a screenshot
excelhours.PNG
What version of Excel are you using?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
If this isn't what you need - please post an Excel sheet - not a screenshot... However, see column AD
If you're using Excel 2003 or before - shout - as the error trapping phase won't work.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Excel 2010
There's an error in my formula. Ignore it for a moment or two...
Back again... Hopefully it's OK now.
BtW, it's an array formula that must be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Try this array formula**:
Assuming the end of range is cell Z2. Adjust to suit.
=AVERAGE(Z2:INDEX(A2:Z2,LARGE(COLUMN(A2:Z2)*ISNUMBER(A2:Z2),12)-COLUMN(A2)+1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Thanks for the replies.
Glenn's seems to work well and is much appreciated.
I'll take a look at yours now Tony.
This would have taken me hours on my own, very grateful to all!
You're welcome. We appreciate the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
Glad to have helped! It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.
Thanks Glenn, works great!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks