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

[Carlee]

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,

 

[Pete_UK]

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

 

[Carlee]

this worked brilliantly. thanks so much.

 

[Pete_UK]

You're welcome - thanks for feeding back.

Pete

this worked brilliantly.  thanks so much.
--
Carlee







- Show quoted text -