Average excluding zeros over a variable range of fixed size

Hi,

I know how to calculate the average excluding zeros. But what I want is something more akin to 'keep going until you find 7 non-zero values, and average that". So if I had a range of 30 items, and was at the 9th cell, I would start averaging from where I was, and go backwards. If I found 7 non-zero values, I would average that, otherwise I would go forwards. If neither worked, I would pick the one that had the highest number of non-zero values to average with.

This sounds great in my head but I have no idea how to implement it x_x. Help would be appreciated.

post an example if you can in an excel sheet

The problem is that I need to apply this to multiple datasets, each of which has a different range that corresponds to my desired number of non-zero values. If I use the whole range, I get more non-zero values than I wanted. If I used a fixed size, say 10, and move along the sheet, it may have more non-zero values, it may have less, it may have none.

i.e. the amount of manual effort required to set the range for each data set is too large. I need excel to be smart about recognizing the range, or work over the whole range and stop when it hits the required number of non-zero values.

click Go Advanced while you are writing a post, then either use the paperclip icon or click on Manage Attachments and follow the steps....to attach a file

reagrds,
abdul

Here's an example data set with example desired values, setting desired number of non-zero values at 7.

yup sixth sense i am with you how to calculate the same on assumption

I'm honestly not sure what more to say about the problem

If you're saying that based on the excel file I attached, well, yes, it makes no sense without my other posts in this thread, because I don't know how to implement exactly what I want without manually setting different ranges every time in each cell.

my pleasure dude....

Originally Posted by :) Sixthsense :)

No worries You just explain us how you are setting the range for each cells? how you are differentiating each ranges for each cells? Just explain the method you are using to calculate the average for each cells, we will take care of building it in formula

Okay, considering 30 cells, say I am at the 15th cell. I want to know the average of 7 non-zero values in the same data set, that lie before the 15th cell.

I can't see the last post here for some reason.... keep clicking pg 2/last and it keeps returning me to page 1

In your User Profile you can adjust the number of posts that you can display for each thread - click on General Settings, then scroll down to Thread Display Options - I have mine set to 40 posts, so there is no problem viewing all the posts in this thread.

Hope this helps.

Pete