| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
| My Column display as "numbers" instead of "alphabets" | Excel Discussion (Misc queries) | |||
| Format cell to display "Y" or "N"when entering a 1 or zero | Excel Discussion (Misc queries) | |||
| embedding "ISERROR" function into an "IF" statement | Excel Worksheet Functions | |||
| How do display a "+" or "-" sign when hiding columns? | Setting up and Configuration of Excel | |||
Hybrid Mode
