0

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

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

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?

  • can you share a copy of your sheet? – user0 Mar 17 at 20:10
0

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:

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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