Where no value, display "NA" - if statement?
 

Good morning all,

Issue: I use the following formula to display data:

=SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$211=DATEVALUE("02/01")),--('Daily Reading Master
Log'!B$3:B$211<=DATEVALUE("2/28")),'Daily Reading Master
Log'!BW$3:BW$211)/SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$211=DATEVALUE("02/01")),--('Daily Reading Master
Log'!B$3:B$211<=DATEVALUE("2/28")))

Problem:
Where this calculation is performed on a set of data that has no values, I
get the 'DIV/0' error. I understand why this occurs, however:

Question:
How can i add an 'IF' component to this equation, such that if the result of
hte calculation is 'DIV/0, display 'N/A', rather than the DIV/0 error

Many thanks in advance,


--
Carlee

Where no value, display "NA" - if statement?
 

You get #DIV/0 when the denominator is zero, so this is what you need
to check for. Try this:

=IF(SUMPRODUCT(--('Daily Reading Master Log'!B$3:B
$211=DATEVALUE("02/01")),--('Daily Reading Master Log'!B$3:B
$211<=DATEVALUE("2/28")))=0,"NA",SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$211=DATEVALUE("02/01")),--('Daily Reading Master Log'!B$3:B
$211<=DATEVALUE("2/28")),'Daily Reading Master Log'!BW$3:BW$211)/
SUMPRODUCT(--('Daily Reading Master Log'!B$3:B
$211=DATEVALUE("02/01")),--('Daily Reading Master Log'!B$3:B
$211<=DATEVALUE("2/28"))))

Hope this helps.

Pete

On Jan 28, 6:15*pm, Carlee wrote:
Good morning all,

Issue: *I use the following formula to display data:

=SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$211=DATEVALUE("02/01")),--('Daily Reading Master
Log'!B$3:B$211<=DATEVALUE("2/28")),'Daily Reading Master
Log'!BW$3:BW$211)/SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$211=DATEVALUE("02/01")),--('Daily Reading Master
Log'!B$3:B$211<=DATEVALUE("2/28")))

Problem:
Where this calculation is performed on a set of data that has no values, I
get the 'DIV/0' error. *I understand why this occurs, however:

Question:
How can i add an 'IF' component to this equation, such that if the result of
hte calculation is 'DIV/0, display 'N/A', rather than the DIV/0 error

Many thanks in advance,

--
Carlee

Where no value, display "NA" - if statement?
 

this worked brilliantly. thanks so much.
--
Carlee


"Pete_UK" wrote:

You get #DIV/0 when the denominator is zero, so this is what you need
to check for. Try this:

=IF(SUMPRODUCT(--('Daily Reading Master Log'!B$3:B
$211=DATEVALUE("02/01")),--('Daily Reading Master Log'!B$3:B
$211<=DATEVALUE("2/28")))=0,"NA",SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$211=DATEVALUE("02/01")),--('Daily Reading Master Log'!B$3:B
$211<=DATEVALUE("2/28")),'Daily Reading Master Log'!BW$3:BW$211)/
SUMPRODUCT(--('Daily Reading Master Log'!B$3:B
$211=DATEVALUE("02/01")),--('Daily Reading Master Log'!B$3:B
$211<=DATEVALUE("2/28"))))

Hope this helps.

Pete

On Jan 28, 6:15 pm, Carlee wrote:
Good morning all,

Issue: I use the following formula to display data:

=SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$211=DATEVALUE("02/01")),--('Daily Reading Master
Log'!B$3:B$211<=DATEVALUE("2/28")),'Daily Reading Master
Log'!BW$3:BW$211)/SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$211=DATEVALUE("02/01")),--('Daily Reading Master
Log'!B$3:B$211<=DATEVALUE("2/28")))

Problem:
Where this calculation is performed on a set of data that has no values, I
get the 'DIV/0' error. I understand why this occurs, however:

Question:
How can i add an 'IF' component to this equation, such that if the result of
hte calculation is 'DIV/0, display 'N/A', rather than the DIV/0 error

Many thanks in advance,

--
Carlee

Where no value, display "NA" - if statement?
 

You're welcome - thanks for feeding back.

Pete

On Jan 28, 7:49*pm, Carlee wrote:
this worked brilliantly. *thanks so much.
--
Carlee



"Pete_UK" wrote:
You get #DIV/0 when the denominator is zero, so this is what you need
to check for. Try this:

=IF(SUMPRODUCT(--('Daily Reading Master Log'!B$3:B
$211=DATEVALUE("02/01")),--('Daily Reading Master Log'!B$3:B
$211<=DATEVALUE("2/28")))=0,"NA",SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$211=DATEVALUE("02/01")),--('Daily Reading Master Log'!B$3:B
$211<=DATEVALUE("2/28")),'Daily Reading Master Log'!BW$3:BW$211)/
SUMPRODUCT(--('Daily Reading Master Log'!B$3:B
$211=DATEVALUE("02/01")),--('Daily Reading Master Log'!B$3:B
$211<=DATEVALUE("2/28"))))

Hope this helps.

Pete

On Jan 28, 6:15 pm, Carlee wrote:
Good morning all,

Issue: *I use the following formula to display data:

=SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$211=DATEVALUE("02/01")),--('Daily Reading Master
Log'!B$3:B$211<=DATEVALUE("2/28")),'Daily Reading Master
Log'!BW$3:BW$211)/SUMPRODUCT(--('Daily Reading Master
Log'!B$3:B$211=DATEVALUE("02/01")),--('Daily Reading Master
Log'!B$3:B$211<=DATEVALUE("2/28")))

Problem:
Where this calculation is performed on a set of data that has no values, I
get the 'DIV/0' error. *I understand why this occurs, however:

Question:
How can i add an 'IF' component to this equation, such that if the result of
hte calculation is 'DIV/0, display 'N/A', rather than the DIV/0 error

Many thanks in advance,

--
Carlee- Hide quoted text -

- Show quoted text -