Formula

[BKG SPORTS]

EXCEL 2007

Trying to find the right formula to get the last avg of 5 in a list

say I have numbers in a1 thru z1

I want the avg of the last 5 numbers in that row

Any help is appreciated
Thanks
BKG SPORTS

 

[Robert Mika]

look at this topic:

http://www.mrexcel.com/forum/showthread.php?t=552250

 

[BKG SPORTS]

No that didn't help me. That formula used names in it, and I didn't understand

 

[Robert Mika]

Is tis not what you after:

 

[BKG SPORTS]

yes like that, but when I avg out the last 5 in your example by hand, it came out to 5.6, and you had 6.6. Just want to make sure

 

[Robert Mika]

Hi
Sorry,
That shoudl have been column I and 9 at the end.
5,4,7,8,9 and the average is 6.6

 

[BKG SPORTS]

ok, that works. what if a cell is blank?

 

[Robert Mika]

Array formula confirm CTRL+SHIFT+ENTER:
=AVERAGE(IV1:INDEX(1:1,LARGE(IF(1:1>0,COLUMN(1:1)),5)))