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

1. 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?

2. 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})))))

3. 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. 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. 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. 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. 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. Re: Formula to work out an average value from a range of cells containing some blanks

Originally Posted by Glenn Kennedy
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.

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