Average of last 12 non zero cells
Hi,
I am trying to work out the average of the last 12 non zero entries of range G3:BT3. The formula is in HD3 (there is also one in HD4)
(It's to workout the average hours worked over last 12 weeks worked)
I can't seem to get it to work? It's attached below.
Anybody have any amazing ideas?
Many many thanks in advance
I didn't download your file.
Assuming there will always be 12 numbers to average, try this array formula**:
=AVERAGE(IF(G3:BT3>0,IF(COLUMN(G3:BT3)>=LARGE(IF(G3:BT3>0,COLUMN(G3:BT3)),12),G3:BT3)))
** 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.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks