# Average Based on Finding The Most Recent Non Blank, Positive Values In Range

1. ## Average Based on Finding The Most Recent Non Blank, Positive Values In Range

Hello all,

In the attached sample, I am hoping to create a formula in cell O3 that will allow me to find the average sales for the most recent "X" number of weeks (noted in cell O1), in each store's row of data, where sales were not blank, not negative (returns), and not zero.

Hopefully this was a good explanation but basically trying to find by row, an average of the a dynamic number of weeks of sales that were not blank, and positive.

2. ## Re: Average Based on Finding The Most Recent Non Blank, Positive Values In Range

copy paste below in O3 then hold control and shift together and then hit enter to make it array formula
Formula:
`Please Login or Register  to view this content.`

drag down

if this helps then click add rep button at the bottom left corner of this post!

3. ## Re: Average Based on Finding The Most Recent Non Blank, Positive Values In Range

Originally Posted by kuschem
copy paste below in O3 then hold control and shift together and then hit enter to make it array formula
Formula:
`Please Login or Register  to view this content.`

drag down

if this helps then click add rep button at the bottom left corner of this post!

That's great. Just out of curiosity (and in case I get asked). How would I tweak the formula if I wanted to capture the first 4 week average of non blank, non zero, non negatives, starting from column B?

4. ## Re: Average Based on Finding The Most Recent Non Blank, Positive Values In Range

Change the large to small

If this helps then add rep by clicking star button at the bottom left corner of this post

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1