EXCELrulez
New Member
- Joined
- Dec 17, 2014
- Messages
- 6
Hello. I am hoping someone can help answer my question. I am working on a spreadsheet that will average 30 numbers, excluding zeros. These numbers are in a column and do include zeros. Below is the formula I have created, but it falls short.
=IF((SUM(F194:F208)=0)," N/A ", (AVERAGEIF((OFFSET(F208,0,0,(SUM(-30-(COUNTIF((OFFSET(F208,0,0,-30)),"=0")))))),">0")))
It will go back 30 rows, count the zeros, and then go back more rows for however many zeros it counted. It will then exclude the zeros from the average. The problem, however, there is no guaranteed it is averaging exactly 30 numbers. When it counts the zeros and extends the range, it picks up more zeros, resulting in an average less than 30 numbers. Would anyone have a suggestion on a formula that would guaranteed an average of 30 numbers? I am willing to look into VBA, if there are any suggestions.
Thanks for your help.
=IF((SUM(F194:F208)=0)," N/A ", (AVERAGEIF((OFFSET(F208,0,0,(SUM(-30-(COUNTIF((OFFSET(F208,0,0,-30)),"=0")))))),">0")))
It will go back 30 rows, count the zeros, and then go back more rows for however many zeros it counted. It will then exclude the zeros from the average. The problem, however, there is no guaranteed it is averaging exactly 30 numbers. When it counts the zeros and extends the range, it picks up more zeros, resulting in an average less than 30 numbers. Would anyone have a suggestion on a formula that would guaranteed an average of 30 numbers? I am willing to look into VBA, if there are any suggestions.
Thanks for your help.