+ Reply to Thread
Results 1 to 8 of 8

Formula to work out an average value from a range of cells containing some blanks

  1. #1
    Registered User
    Join Date
    09-12-2017
    Location
    Liverpool
    MS-Off Ver
    Not known
    Posts
    9

    Formula to work out an average value from a range of cells containing some blanks

    Hi,

    Please can you help with this?

    I need a formula that will work out an average value from a range of values which contain some blanks.

    For example:

    1 2 3 4 5 6
    5 0 - 1 - 8

    Row A contains weeks 1-6 (where week 1 is the most recent week and week 6 is the least recent week), row B contain a range of values for each of those weeks. I need a formula that will work out the average value for the three most recent weeks where there is a value present.

    For example, the average value for the most recent 3 weeks with a value in row B is 2 (the sum of 5 plus 0 plus 1 (the formula should disregard the cell in the third column where there is no value present) divided by 3 = 2).

    Please can anybody advise of a formula that will do this for me?

    Thanks for your help.
    Last edited by Jonny_71; 09-12-2017 at 09:21 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,858

    Re: Formula to work out an average value from a range of cells containing some blanks

    There are LOTS of ways to do this. Here's one:

    =AVERAGE(INDEX(2:2,N(IF(1,SMALL(INDEX((A2:F2<>"")*(COLUMN(A2:F2)),0),{1,2,3})))))
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    09-12-2017
    Location
    Liverpool
    MS-Off Ver
    Not known
    Posts
    9

    Re: Formula to work out an average value from a range of cells containing some blanks

    Hi Glenn,

    Thanks very much for your reply. Apologies for being a bit dumb but why does the formula return a value of 1.66 when the average for the quoted example above should return a value of 2, i.e. (5 + 0 +1)/3 = 2?

    Thanks.

  4. #4
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    2,989

    Re: Formula to work out an average value from a range of cells containing some blanks

    the formula needs to be entered as an array goto cell b7, click on it and press shift control enter at the same time.

    =AVERAGEIF(A1:F1,"<="&SMALL(IF(A2:F2<>"",A1:F1,999999),3),A2:F2)

  5. #5
    Registered User
    Join Date
    09-12-2017
    Location
    Liverpool
    MS-Off Ver
    Not known
    Posts
    9

    Re: Formula to work out an average value from a range of cells containing some blanks

    Hi davsth,

    Thanks very much for your reply. I've tried you're suggestion but when I put the formula in I get the message "too many arguments were entered for this function". I am doing something wrong?

    Thanks.

  6. #6
    Registered User
    Join Date
    09-12-2017
    Location
    Liverpool
    MS-Off Ver
    Not known
    Posts
    9

    Re: Formula to work out an average value from a range of cells containing some blanks

    Thanks davsth, got it working now. Thanks very much for your help.

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    33,858

    Re: Formula to work out an average value from a range of cells containing some blanks

    Simple answer: 'cos I'm being thick.

    I know you're sorted, but this array formula does not require the presence of 1-6 in row 1:

    =AVERAGE(INDEX(A2:F2,SMALL(IF(A2:F2<>"",COLUMN(A2:F2)),1)):INDEX(A2:F2,SMALL(IF(A2:F2<>"",COLUMN(A2:F2)),3)))

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...

  8. #8
    Registered User
    Join Date
    09-12-2017
    Location
    Liverpool
    MS-Off Ver
    Not known
    Posts
    9

    Re: Formula to work out an average value from a range of cells containing some blanks

    Quote Originally Posted by Glenn Kennedy View Post
    Simple answer: 'cos I'm being thick.

    I know you're sorted, but this array formula does not require the presence of 1-6 in row 1:

    =AVERAGE(INDEX(A2:F2,SMALL(IF(A2:F2<>"",COLUMN(A2:F2)),1)):INDEX(A2:F2,SMALL(IF(A2:F2<>"",COLUMN(A2:F2)),3)))

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Great, thanks very much Glenn.

+ 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. Replies: 14
    Last Post: 04-25-2017, 03:11 AM
  2. Average formula for last 15 days excluding blanks
    By jimbob23 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-22-2014, 11:50 AM
  3. Replies: 5
    Last Post: 10-21-2013, 10:56 AM
  4. [SOLVED] Is it possible to have sub totals work between 2 blanks cells?
    By gjjh25 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-27-2013, 09:39 AM
  5. Average Formula of Cells Over Certain Value Excludes Blanks
    By larzep in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-16-2012, 08:36 PM
  6. Average ignoring Blanks cells
    By Justinmih in forum Excel General
    Replies: 10
    Last Post: 09-30-2011, 06:21 PM
  7. Replies: 1
    Last Post: 01-25-2011, 10:37 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