Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum(indirect(Address......
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(RO W(),MATCH(LOOKUP(100^100,$A10:$FO10),$A10:$FO10,FA LSE)))) 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!!!! -- Kevin Smith :o) |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
Indirect address | Excel Discussion (Misc queries) | |||
INDIRECT and ADDRESS | Excel Worksheet Functions | |||
using INDIRECT(ADDRESS(...)) | Excel Discussion (Misc queries) | |||
Indirect(Address(... | Excel Discussion (Misc queries) |