-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Average over multiple ranges - excluding zeros.
- Thread starter locodice
- Start date
Sandeep Warrier
Well-known Member
- Joined
- Oct 31, 2008
- Messages
- 2,679
What do you mean by multiple ranges? Multiple columns or non-contiguous ranges (eg: A1:D10 or A1:A10,C1:C10 etc)?
If it is the former then try
with data in A1:D14
confirm with Control+Shift+Enter instead of just Enter as you would usually do. If done properly Excel will place {} around the formula.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
If it is the former then try
with data in A1:D14
Code:
=AVERAGE(IF($A$1:$D$14<>0,$A$1:$D$14))confirm with Control+Shift+Enter instead of just Enter as you would usually do. If done properly Excel will place {} around the formula.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Would you specify which ranges you want to average?
Upvote
0
Aladin Akyurek
MrExcel MVP
- Joined
- Feb 14, 2002
- Messages
- 85,210
Assuming that there aren't any negative numbers in the ranges of interest, try...
Code:
=SUM(A2:AB25,C2:AB25,E2:AB25)/
INDEX(FREQUENCY((A2:AB25,C2:AB25,E2:AB25),0),2)
Upvote
0
Similar threads
- Question
- Question
