Average of the last three

[Kinghart]

Hello Guys... Can Anyone help me with this


# A B C D E F - - - K

1 23 25 0 0 80 52 ?

2


In K1 I Want to find the average of the last 3 figures excluding 0... meaning the average of BEF (25+80+52 / 3) and continue the formula as i add a new figure to g1....

Say if G1=62 then the formula in K1 should calculate the average of EFG (80+52+62 / 3)

 

[keymaster]

Interesting question...


Here is a solution that uses helper cells


http://chandoo.org/img/playground/average-of-last3.xlsx


The idea is like this:


We create a similar grid of helper cells, and fill them with number 1 thru n. Incrementing the number if the original value is not zero, otherwise keeping it same.


So, for 23 25 0 0 80 52

the corresponding numbers will be

1 2 2 2 3 4


Then, we find the position of 2 (since 4 is the largest number and we need 3rd number from 4, thus 2) using MATCH.


Once found, we just calculate the sum of all numbers from that position till end in our original list and divide it by 3 to get the average.


I would love to know if there is way to do this with out helper cells.

 

[Hui]

If 23 is in A1

and you delete the 0 so the cells are blank, then

Array entered =AVERAGE(INDEX(1:1,,LARGE(IF($A1:F1<>"",COLUMN($A:F)),$A2)):F1) Ctrl Shift Enter

Where A2 has the value 3 will do the trick

 

[oldchippy]

Hi,


I've been searching the web and adapted a formula I found to produce this which seems to work


=AVERAGE(IF(COLUMN(1:1)>=LARGE(IF(1:1,COLUMN(1:1)),3),IF(1:1,1:1)))


Entered as Ctrl+Shift+Enter


But can you explain Hui how it works?


BTW I found the formula here http://www.xl-central.com/average-last-3-nonzero-values.html

 

[Kinghart]

thanks guys... have another problem.... in the range in which there is 1 number or 2, the formula is resulting in #NUM! error... only when i put a 3rd number it shows the average... I want it to show the average even if I put 1 number or 2... it should take maximum of 3 numbers which is the latest three to show the avarage...

 

[oldchippy]

Try this one,


=AVERAGE(IF(COLUMN(1:1)>=LARGE(IF(1:1,COLUMN(1:1)),MIN(3,COUNT(1:1))),IF(1:1,1:1)))


Ctrl+Shift+Enter

 

[Kinghart]

hello

thanks chippy.. but a little problem

it does calculate even when there are 2 numbers or one... but only when the other cells are blanks... can't even put a zero there.... but the blanks should be zeroes :-(

 

[oldchippy]

Hi again,


=AVERAGE(IF(COLUMN(1:1)>=LARGE(IF(1:1,COLUMN(1:1)),MIN(3,COUNTIF(1:1,">0"))),IF(1:1,1:1)))


C+S+E

 

[Kinghart]

that solve the "0" problem but it takes the average of all the numbers.... i want to get the average of the last 3 numbers only as i keep adding new columns

 

[oldchippy]

Well it works for me - average the last three numbers above zero?