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

=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

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

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