MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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)