I have a workbook that contains 5 tabs of data and an Analysis tab that compiles the data. On my "Analysis" tab the user chooses the date they would like to see (e.g. 1/1/2014), I am trying to average the last 6 months of data based on the date chosen (July 2013 - Decemeber 2013). The only thing is that between Dec 2013 and Jan 2014 is a column that is totaling the 2013 data. I am trying to skip this column when averaging the data. I have been unsuccessful in doing so. Any help is appreciated.
The formula that I am using is:
=(VLOOKUP(VLOOKUP($A10,Lookup_Sheet!$A:$E,MATCH(C$6,Lookup_Sheet!$A$3:$E$3,0),FALSE),INDIRECT("'"&C$6&"'!A:OA"),MATCH($B$2,INDIRECT("'"&C$6&"'!B6:OA6"),0),FALSE)+VLOOKUP(VLOOKUP($A10,Lookup_Sheet!$A:$E,MATCH(C$6,Lookup_Sheet!$A$3:$E$3,0),FALSE),INDIRECT("'"&C$6&"'!A:OA"),MATCH($B$2,INDIRECT("'"&C$6&"'!B6:OA6"),0)-1,FALSE)+VLOOKUP(VLOOKUP($A10,Lookup_Sheet!$A:$E,MATCH(C$6,Lookup_Sheet!$A$3:$E$3,0),FALSE),INDIRECT("'"&C$6&"'!A:OA"),MATCH($B$2,INDIRECT("'"&C$6&"'!B6:OA6"),0)-2,FALSE)+VLOOKUP(VLOOKUP($A10,Lookup_Sheet!$A:$E,MATCH(C$6,Lookup_Sheet!$A$3:$E$3,0),FALSE),INDIRECT("'"&C$6&"'!A:OA"),MATCH($B$2,INDIRECT("'"&C$6&"'!B6:OA6"),0)-3,FALSE)+VLOOKUP(VLOOKUP($A10,Lookup_Sheet!$A:$E,MATCH(C$6,Lookup_Sheet!$A$3:$E$3,0),FALSE),INDIRECT("'"&C$6&"'!A:OA"),MATCH($B$2,INDIRECT("'"&C$6&"'!B6:OA6"),0)-4,FALSE)+VLOOKUP(VLOOKUP($A10,Lookup_Sheet!$A:$E,MATCH(C$6,Lookup_Sheet!$A$3:$E$3,0),FALSE),INDIRECT("'"&C$6&"'!A:OA"),MATCH($B$2,INDIRECT("'"&C$6&"'!B6:OA6"),0)-5,FALSE))/6
The issue is that column "N" or Column "13" is the Totals column and I do not want that column to be used in the calculation. I would like the column to be skipped.
Bookmarks