Monthly, Quarterly formulas
 

Looking for formula help. Here's the situation:
Excel doc using one tab per month and then a quarterly (ex. 01.07, 02.07,
03.07, Q1)
Cell E5 references a dollar amount in each monthly tab.
Cell E5 in Q1 tab needs to average each month's E5
('01.07'!E5+'02.07'!E5+'03.07'!E5/3)

What I need, is a formula to exclude zero values in the event the month
"hasn't happend yet".
If it's January and I've input a value in '01.07!E5' of $100, and values in
'02.07'!E5 & '03.07'!E5 tabs are zero (because the months haven't happened
yet...
My original formula divides $100 by 3; I only want it to divide by 1.
If it's February and I've input a value in '01.07!E5' of $100, and a value
in '02.07!E5' of $150, and 0 value in '03.07'!E5 (because the month hasn't
happened yet...
My original formula divides $250 by 3; I only want it to divide by 2.

Any suggestions?
(have I over explained to confusion yet?)
-Bobby

Monthly, Quarterly formulas
 

If "hasn't happend yet" values were blank rather than 0 then:

=Average('01.07'!E5,'02.07'!E5,'03.07'!E5)


"BobbyVZW" wrote:

Looking for formula help. Here's the situation:
Excel doc using one tab per month and then a quarterly (ex. 01.07, 02.07,
03.07, Q1)
Cell E5 references a dollar amount in each monthly tab.
Cell E5 in Q1 tab needs to average each month's E5
('01.07'!E5+'02.07'!E5+'03.07'!E5/3)

What I need, is a formula to exclude zero values in the event the month
"hasn't happend yet".
If it's January and I've input a value in '01.07!E5' of $100, and values in
'02.07'!E5 & '03.07'!E5 tabs are zero (because the months haven't happened
yet...
My original formula divides $100 by 3; I only want it to divide by 1.
If it's February and I've input a value in '01.07!E5' of $100, and a value
in '02.07!E5' of $150, and 0 value in '03.07'!E5 (because the month hasn't
happened yet...
My original formula divides $250 by 3; I only want it to divide by 2.

Any suggestions?
(have I over explained to confusion yet?)
-Bobby

Monthly, Quarterly formulas
 

use Average instead.

"BobbyVZW" wrote:

Looking for formula help. Here's the situation:
Excel doc using one tab per month and then a quarterly (ex. 01.07, 02.07,
03.07, Q1)
Cell E5 references a dollar amount in each monthly tab.
Cell E5 in Q1 tab needs to average each month's E5
('01.07'!E5+'02.07'!E5+'03.07'!E5/3)

What I need, is a formula to exclude zero values in the event the month
"hasn't happend yet".
If it's January and I've input a value in '01.07!E5' of $100, and values in
'02.07'!E5 & '03.07'!E5 tabs are zero (because the months haven't happened
yet...
My original formula divides $100 by 3; I only want it to divide by 1.
If it's February and I've input a value in '01.07!E5' of $100, and a value
in '02.07!E5' of $150, and 0 value in '03.07'!E5 (because the month hasn't
happened yet...
My original formula divides $250 by 3; I only want it to divide by 2.

Any suggestions?
(have I over explained to confusion yet?)
-Bobby