If statement trouble

C

Carlee

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?
 
T

Teethless mama

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

ctrl+shift+enter, not just enter
 
C

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
 
T

Teethless mama

You can use a cell reference


Carlee said:
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
Click to expand...
 
Top