# 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?  Register To Reply

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})))))  Register To Reply

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.  Register To Reply

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)  Register To Reply

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.  Register To Reply

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.  Register To Reply

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...  Register To Reply

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.  Register To Reply