Formula Question
Posted by Kevin Smith on January 07, 2002 7:11 AM
I enter several rows of data. My need is to compute an average of the last seven entries in a column. However I want to ignore any zeroes. Example if mu last 10 entries were 1,2,3,4,5,0,0,6,7,8. I would want the average of 2,3,4,5,6,7,8. Any help would be appreciated.
Posted by IML on January 07, 2002 7:32 AM
You could use
=SUMIF(A1:A10,"<>"&0)/COUNTIF(A1:A10,"<>"&0)
if you have blank cells, you may need to clean those up or use the following array formula (hit control shift enter)
=AVERAGE(IF(A1:A10,A1:A10))
The first formula would be preferable.
Posted by IML on January 07, 2002 7:37 AM
Now I'll read your question and see if I can answer that. (sorry)
Posted by Mark W. on January 07, 2002 8:13 AM
See 13475.html (nt)
