+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    11-20-2015
    Location
    Rogers
    MS-Off Ver
    365
    Posts
    23

    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.


    Thanks in advance
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    06-13-2018
    Location
    india
    MS-Off Ver
    2010;2007
    Posts
    54

    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: copy to clipboard
    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!
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-20-2015
    Location
    Rogers
    MS-Off Ver
    365
    Posts
    23

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

    Quote Originally Posted by kuschem View Post
    copy paste below in O3 then hold control and shift together and then hit enter to make it array formula
    Formula: copy to clipboard
    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. #4
    Registered User
    Join Date
    06-13-2018
    Location
    india
    MS-Off Ver
    2010;2007
    Posts
    54

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] calculate average if all values are positive and move data based if located
    By thedrinkerparadox in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-17-2017, 10:31 AM
  2. [SOLVED] Finding the average of positive numbers base on the previous date
    By top00001 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-27-2015, 03:54 AM
  3. [SOLVED] Finding average value for the positive and negative set of numbers
    By thilag in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-26-2014, 04:39 AM
  4. Average based on last 7 values (non-blank/zero)?
    By nobodyukno in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-06-2014, 04:34 PM
  5. Replies: 1
    Last Post: 04-29-2014, 09:06 PM
  6. calculate average of positive values only
    By umbata in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 07-17-2013, 08:38 AM
  7. Summing product based off multiple dates and finding most recent
    By CWicklund in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-26-2012, 06:44 PM

Bookmarks

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