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?