I am trying to get the average of data every other row, M4:M16. I am able to get the average of that going every other row using, =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,AVERAGE(IF(ISNUMBER(M3:M22)*(MOD(ROW(M3:M22),2)=0),M3:M22)))), and hitting ctrl+shift+enter. I am wondering if there is a way to do this and filter out the 0's so it doesn't average the 0 as well. Right now if I put a 25 in one of the days I would like it to say 25 instead of 3.5714285.
Let me ask ya a question...
If you want to average the range M4:M16 why is your formula referencing the range M3:M22?
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
This is a new formula to me and was just grabbing a random range to see if it worked. I will be changing it to the correct rows.
Try one of these array formulas**:
Data Range
If you will never insert new rows before the range:
=AVERAGE(IF(MOD(ROW(M4:M16),2)=0,IF(M4:M16<>0,M4:M16)))
If you might insert new rows before the range:
=AVERAGE(IF(MOD(ROW(M4:M16)-ROW(M4),2)=0,IF(M4:M16<>0,M4:M16)))
Both formulas are array formulas.
** 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.
Thank you so much. This worked great!
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks