I’m doing something that’s kind of strange but it makes sense for what I’m trying to accomplish. But I need some help. I have 3 worksheets with company data from 3 separate years. I am trying to roll this data up. I am doing this by using VLookups and then averaging the three values that are returned. So: =(VLOOKUP($A3,‘2007’!$A:$U,D$1,FALSE)+VLOOKUP($A3,‘2008’!$A:$U,D$1,FALSE)+VLOOKUP($A3,‘2009’!$A:$U,D$1,FALSE))/3 This works. However, this only works if a company has participated in all three years. Presumably if the formula errors out it’s because the company didn’t exist in the sample one of the years. And logic would say that that year would be the earliest year. So here’s what I’m wondering: Is it possible to have a formula be “dependent” upon itself. For example: The formula calculates the average of the 2007, 2008, and 2009 data. If this value is an error (meaning one or more of the years vlookups error’d out) I want it to know to drop the earliest year and recompute just the average of 2008 and 2008. And continuing, if this new average of 2008 & 2009 error’d out as well to just display the value for 2009. Does that make sense? Basically they would be a series of if statements dependent upon a series of embedded formulas results… Not sure how to write this or if it’s even possible… Please advise. thanks!!!
Just use if statements… like if(vlookup>0,vlookup,0). For the denominator instead of 3 have (if(vlookup>0,1,0) +… More or less have denominator sum up to the number of terms returning values. Should be simple.
I might be missing a parentheses somewhere but this is the general idea. if(isna(VLOOKUP($A3,‘2007’!$A:$U,D$1,FALSE),if(isna(VLOOKUP($A3,‘2008’!$A:$U,D$1,FALSE),VLOOKUP($A3,‘2009’!$A:$U,D$1,FALSE),(VLOOKUP($A3,‘2008’!$A:$U,D$1,FALSE)+VLOOKUP($A3,‘2009’!$A:$U,D$1,FALSE))/2),(VLOOKUP($A3,‘2007’!$A:$U,D$1,FALSE)+VLOOKUP($A3,‘2008’!$A:$U,D$1,FALSE)+VLOOKUP($A3,‘2009’!$A:$U,D$1,FALSE))/3)
absolutely, I just helped someone do something similar. you’d first test for the presence of an error in your first result using IF and ISNA. If the result of hte lookup is NOT an error, return the result of the lookup, else return 0. you could use nested IF statements, or how about something like below. =SUM(IF(ISNA(VLOOKUP($A3,‘2007’!$A:$U,D$1,FALSE)),0,VLOOKUP($A3,‘2007’!$A:$U,D$1,FALSE)),IF(ISNA(VLOOKUP($A3,‘2008’!$A:$U,D$1,FALSE)),0,VLOOKUP($A3,‘2008’!$A:$U,D$1,FALSE)),IF(ISNA(VLOOKUP($A3,‘2009’!$A:$U,D$1,FALSE)),0,VLOOKUP($A3,‘2009’!$A:$U,D$1,FALSE)))/SUM(NOT(ISNA(VLOOKUP($A3,‘2007’!$A:$U,D$1,FALSE))),NOT(ISNA(VLOOKUP($A3,‘2008’!$A:$U,D$1,FALSE))),NOT(ISNA(VLOOKUP($A3,‘2009’!$A:$U,D$1,FALSE))))
Perfect thanks everyone! I got it working 
DoubleDip Wrote: ------------------------------------------------------- > absolutely, I just helped someone do something > similar. you’d first test for the presence of an > error in your first result using IF and ISNA. If > the result of hte lookup is NOT an error, return > the result of the lookup, else return 0. you > could use nested IF statements, or how about > something like below. > > =SUM(IF(ISNA(VLOOKUP($A3,‘2007’!$A:$U,D$1,FALSE)), > 0,VLOOKUP($A3,‘2007’!$A:$U,D$1,FALSE)),IF(ISNA(VLO > OKUP($A3,‘2008’!$A:$U,D$1,FALSE)),0,VLOOKUP($A3,‘2 > 008’!$A:$U,D$1,FALSE)),IF(ISNA(VLOOKUP($A3,‘2009’! > $A:$U,D$1,FALSE)),0,VLOOKUP($A3,‘2009’!$A:$U,D$1,F > ALSE)))/SUM(NOT(ISNA(VLOOKUP($A3,‘2007’!$A:$U,D$1, > FALSE))),NOT(ISNA(VLOOKUP($A3,‘2008’!$A:$U,D$1,FAL > SE))),NOT(ISNA(VLOOKUP($A3,‘2009’!$A:$U,D$1,FALSE) > ))) You could use “sumif” instead and you wouldn’t even need the ISNA stuff. Doing it this way, if it doesn’t find anything it will automatically assume 0. Then for the denominator you could use a “countif” function to solve for instances where there is no data for that particular year. Not using the exact data from above, conceptually it would be something like: =(sumif(2007 Range,Criteria,2007 Sum Range)+sumif(2008 Range,Criteria,2008 Sum Range)+sumif(2009 Range,Criteria,2009 Sum Range))/(countif(2007 Range,Criteria)+countif(2008 Range,Criteria)+countif(2009 Range,Criteria))
nice!