ExcelBanter - Formula Question

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Formula Question (https://www.excelbanter.com/excel-discussion-misc-queries/63284-formula-question.html)

Formula Question

Hi all,

I am trying to calcuate the average for cell C17 in 12 different sheets
within the same spreadsheet. This is what I am using. But its not working if
there is an empty cell and its not averaging correctly. Any ideas?

=IF(ISERROR(AVERAGE(Jan06!C17)+(Feb06!C17)+(Mar06! C17)+(Apr06!C17)+(May06!C17)+(Jun06!C17)+(July06!C 17)+(Aug06!C17)+(Sept06!C17)+(Oct06!C17)+(Nov06!C1 7)+(Dec06!C17)),"",(Jan06!C17)+(Feb06!C17)+(Mar06! C17)+(Apr06!C17)+(May06!C17)+(Jun06!C17)+(July06!C 17)+(Aug06!C17)+(Sept06!C17)+(Oct06!C17)+(Nov06!C1 7)+(Dec06!C17))

Thanks for any help!!!
--
Bruce DiPaola

Formula Question

For starters, don't average (A+B+C...) (which averages one number - the sum
of all the cells) but rather (A,B,C) (which averages the three cells).
And, to make your life a lot easier, you can probably use a '3D' cell
reference:
=average(Jan06:Dec06!C17). That assumes that the Jan06 through Dec06 are
consecutive sheets in your workbook.
--Bruce

"Bruce D." wrote:

Hi all,

I am trying to calcuate the average for cell C17 in 12 different sheets
within the same spreadsheet. This is what I am using. But its not working if
there is an empty cell and its not averaging correctly. Any ideas?

=IF(ISERROR(AVERAGE(Jan06!C17)+(Feb06!C17)+(Mar06! C17)+(Apr06!C17)+(May06!C17)+(Jun06!C17)+(July06!C 17)+(Aug06!C17)+(Sept06!C17)+(Oct06!C17)+(Nov06!C1 7)+(Dec06!C17)),"",(Jan06!C17)+(Feb06!C17)+(Mar06! C17)+(Apr06!C17)+(May06!C17)+(Jun06!C17)+(July06!C 17)+(Aug06!C17)+(Sept06!C17)+(Oct06!C17)+(Nov06!C1 7)+(Dec06!C17))

Thanks for any help!!!
--
Bruce DiPaola

Formula Question

Yes That's it.
Many thanks

--
Bruce DiPaola

"Bruce D." wrote:

Hi all,

I am trying to calcuate the average for cell C17 in 12 different sheets
within the same spreadsheet. This is what I am using. But its not working if
there is an empty cell and its not averaging correctly. Any ideas?

=IF(ISERROR(AVERAGE(Jan06!C17)+(Feb06!C17)+(Mar06! C17)+(Apr06!C17)+(May06!C17)+(Jun06!C17)+(July06!C 17)+(Aug06!C17)+(Sept06!C17)+(Oct06!C17)+(Nov06!C1 7)+(Dec06!C17)),"",(Jan06!C17)+(Feb06!C17)+(Mar06! C17)+(Apr06!C17)+(May06!C17)+(Jun06!C17)+(July06!C 17)+(Aug06!C17)+(Sept06!C17)+(Oct06!C17)+(Nov06!C1 7)+(Dec06!C17))

Thanks for any help!!!
--
Bruce DiPaola