I have a data set that's separated by year (so, the first year is Sheet1, the second year is Sheet2, and so on. There are 18 years in total). I want to average the data from different years. At first, I used


which returned the value 13034.66667. This formula is long and unwieldy though, so I instead tried

=iferror(AVERAGE((Sheet1!B2), (Sheet2!B2), (Sheet3!B2), (Sheet4!B2), (Sheet5!B2),(Sheet6!B2),(Sheet7!B2),(Sheet8!B2),(Sheet9!B2), (Sheet10!B2),(Sheet11!B2),(Sheet12!B2),(Sheet13!B2),(Sheet14!B2), (Sheet15!B2),(Sheet16!B2),(Sheet17!B2),(Sheet18!B2)))

but this returned a value of 15641.6. I'm not sure why the two different formulas returned different values, can someone explain to me what's happening here?


1 Answer 1


1. =AVERAGE(IFERROR(Sheet1!B2),IFERROR(Sheet2!B2),IFERROR(Sheet3!B2))
2. =IFERROR(AVERAGE(Sheet1!B2,Sheet2!B2,Sheet3!B2))

These two formulas are not the same. The 1st one will skip individual errors per every mentioned sheet while the 2nd one will turn blank if any of the mentioned sheets output an error.

On a small scale, it can be illustrated here:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.