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

if you have blank cells, you may need to clean those up or use the following array formula (hit control shift enter)

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)