If statement trouble
 

Hi there,

I use the following function to sum values in a specified range on the
'Daily Reading Master Log', and place the result in a column on the 'Actual
vs Budget' sheet.

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B29=DATEVALUE("01/06")),--('Daily Reading Master
Log'!B3:B29<=DATEVALUE("30/06")),'Daily Reading Master
Log'!BM3:BM29)/SUMPRODUCT(--('Daily Reading Master
Log'!B3:B29=DATEVALUE("01/06")),--('Daily Reading Master
Log'!B3:B29<=DATEVALUE("30/06")))

Problem:
If the result the function produces is null (because the date range is in
the future and therefore no data yet available), I want the function to
product a '0', otherwise, run the function normally.

Can anyone help me out?

--
Carlee

If statement trouble
 

=AVERAGE(IF(TEXT('Daily Reading Master Log'!B3:B29,"mmyyyy")="062007",'Daily
Reading Master Log'!BM3:BM29))

ctrl+shift+enter, not just enter


"Carlee" wrote:

Hi there,

I use the following function to sum values in a specified range on the
'Daily Reading Master Log', and place the result in a column on the 'Actual
vs Budget' sheet.

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B29=DATEVALUE("01/06")),--('Daily Reading Master
Log'!B3:B29<=DATEVALUE("30/06")),'Daily Reading Master
Log'!BM3:BM29)/SUMPRODUCT(--('Daily Reading Master
Log'!B3:B29=DATEVALUE("01/06")),--('Daily Reading Master
Log'!B3:B29<=DATEVALUE("30/06")))

Problem:
If the result the function produces is null (because the date range is in
the future and therefore no data yet available), I want the function to
product a '0', otherwise, run the function normally.

Can anyone help me out?

--
Carlee

If statement trouble
 

Hi there,
Can this option you've provided be adapted such that the dates '062007' are
not fixed? These sheet will be used for the next three years at least

--
Carlee


"Teethless mama" wrote:

=AVERAGE(IF(TEXT('Daily Reading Master Log'!B3:B29,"mmyyyy")="062007",'Daily
Reading Master Log'!BM3:BM29))

ctrl+shift+enter, not just enter


"Carlee" wrote:

Hi there,

I use the following function to sum values in a specified range on the
'Daily Reading Master Log', and place the result in a column on the 'Actual
vs Budget' sheet.

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B29=DATEVALUE("01/06")),--('Daily Reading Master
Log'!B3:B29<=DATEVALUE("30/06")),'Daily Reading Master
Log'!BM3:BM29)/SUMPRODUCT(--('Daily Reading Master
Log'!B3:B29=DATEVALUE("01/06")),--('Daily Reading Master
Log'!B3:B29<=DATEVALUE("30/06")))

Problem:
If the result the function produces is null (because the date range is in
the future and therefore no data yet available), I want the function to
product a '0', otherwise, run the function normally.

Can anyone help me out?

--
Carlee

If statement trouble
 

You can use a cell reference


"Carlee" wrote:

Hi there,
Can this option you've provided be adapted such that the dates '062007' are
not fixed? These sheet will be used for the next three years at least

--
Carlee


"Teethless mama" wrote:

=AVERAGE(IF(TEXT('Daily Reading Master Log'!B3:B29,"mmyyyy")="062007",'Daily
Reading Master Log'!BM3:BM29))

ctrl+shift+enter, not just enter


"Carlee" wrote:

Hi there,

I use the following function to sum values in a specified range on the
'Daily Reading Master Log', and place the result in a column on the 'Actual
vs Budget' sheet.

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B29=DATEVALUE("01/06")),--('Daily Reading Master
Log'!B3:B29<=DATEVALUE("30/06")),'Daily Reading Master
Log'!BM3:BM29)/SUMPRODUCT(--('Daily Reading Master
Log'!B3:B29=DATEVALUE("01/06")),--('Daily Reading Master
Log'!B3:B29<=DATEVALUE("30/06")))

Problem:
If the result the function produces is null (because the date range is in
the future and therefore no data yet available), I want the function to
product a '0', otherwise, run the function normally.

Can anyone help me out?

--
Carlee