Sum(indirect(Address......

[Kevin Smith]

Hello,

I am trying to produce a formula that will give the last 4 weeks average
from a Row that contains the daily figures followed by the weely averages i.e
A B C D E F G H I J K

1 Sun Mon Tue Wed Thu Fri Sat Average Sun Mon Tue etc etc
2 5 4 5 4 5 4 5 4.5714 5 5 4
etc etc

The weekly average is setup so if there is no data then it returns "". What
i am doing is finding the last active cell on the column and then using that
to set the cell range for the sum and count to produce the averages.

This is a snippet of the formula that i am using


=SUM(INDIRECT(ADDRESS(ROW(),MATCH(LOOKUP(100^100,$A10:$FO10),$A10:$FO10,FALSE))):INDIRECT(ADDRESS(ROW(),MATCH(LOOKUP(100^100,$A10:$FO10),$A10:$FO10,FALSE))))

The problem that i have is... the lookup runs right to left but the match
runs left to right. Therefore the lookup finds the value of the last cell
which should be the average of the current week but then if there is another
instance of the lookup value on the same row then the Match stops at that
cell.

is there a way to get Match to run Right to Left in line with the lookup or
is there an easier way to do what i am attempting?

Please HELP!!!!

 

[Jacob Skaria]

Try the below

Array entered (Apply formul using Ctrl+Shift+Enter instead of just Enter
=SUM(LOOKUP(LARGE(IF((ISNUMBER(2:2)*(1:1="Average")),COLUMN(2:2)),ROW(1:4)),COLUMN(2:2),2:2))/4

Make sure you have atleast 4 average cells ..The formula picks up values of
the last 4 cells in Row 2 the corresponding cells in Row1 contains the text
'Average'

 

[Jacob Skaria]

OR..
=AVERAGE(LOOKUP(LARGE(IF((ISNUMBER(2:2)*(1:1="Average")),
COLUMN(2:2)),ROW(1:4)),COLUMN(2:2),2:2))

 

[Kevin Smith]

Thank you for your assistance that is spot on.