| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I make a cell blank if Error OR 0 Result
Currently Cell 'B4' in my worksheet contains the following formula. Now this
formula WORKS. I just want to refine it a tad more. Currently, if cells AE4,AF4,and AG4 contain and error, then B4 is blank. BUT, if AE4, AF4, and AG4 are blank, then B$ shows a "0". Which is correct. I would prefer that B$ also be blank in the second senario. How can I modify this formula for that behavior? =IF(ISERROR(SUM(SUM(INDIRECT("AE"&ROW())),(INDIREC T("AF"&ROW())),(INDIRECT("AG"&ROW())))/(SUM(COLUMNS((INDIRECT("D"&ROW()):(INDIRECT("AA"&R OW()))))-(COUNTIF(INDIRECT("D"&ROW()):(INDIRECT("AA"&ROW()) ),"N/A"))))),"",SUM(SUM(INDIRECT("AE"&ROW())),(INDIRECT ("AF"&ROW())),(INDIRECT("AG"&ROW())))/(SUM(COLUMNS((INDIRECT("D"&ROW()):(INDIRECT("AA"&R OW()))))-(COUNTIF(INDIRECT("D"&ROW()):(INDIRECT("AA"&ROW()) ),"N/A"))))) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I make a cell blank if Error OR 0 Result
You could cheat by putting conditional formatting on the cell that if the
value is 0 then change the font to white so that it remains as a 0 but simply can't be seen. "Gregory Day" wrote: Currently Cell 'B4' in my worksheet contains the following formula. Now this formula WORKS. I just want to refine it a tad more. Currently, if cells AE4,AF4,and AG4 contain and error, then B4 is blank. BUT, if AE4, AF4, and AG4 are blank, then B$ shows a "0". Which is correct. I would prefer that B$ also be blank in the second senario. How can I modify this formula for that behavior? =IF(ISERROR(SUM(SUM(INDIRECT("AE"&ROW())),(INDIREC T("AF"&ROW())),(INDIRECT("AG"&ROW())))/(SUM(COLUMNS((INDIRECT("D"&ROW()):(INDIRECT("AA"&R OW()))))-(COUNTIF(INDIRECT("D"&ROW()):(INDIRECT("AA"&ROW()) ),"N/A"))))),"",SUM(SUM(INDIRECT("AE"&ROW())),(INDIRECT ("AF"&ROW())),(INDIRECT("AG"&ROW())))/(SUM(COLUMNS((INDIRECT("D"&ROW()):(INDIRECT("AA"&R OW()))))-(COUNTIF(INDIRECT("D"&ROW()):(INDIRECT("AA"&ROW()) ),"N/A"))))) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How do I make a cell blank if Error OR 0 Result
Thank you for the suggestion, but wouldn't that may create issues with other
formulas that use those cells. Averaging for example. "Dennis" wrote: You could cheat by putting conditional formatting on the cell that if the value is 0 then change the font to white so that it remains as a 0 but simply can't be seen. "Gregory Day" wrote: Currently Cell 'B4' in my worksheet contains the following formula. Now this formula WORKS. I just want to refine it a tad more. Currently, if cells AE4,AF4,and AG4 contain and error, then B4 is blank. BUT, if AE4, AF4, and AG4 are blank, then B$ shows a "0". Which is correct. I would prefer that B$ also be blank in the second senario. How can I modify this formula for that behavior? =IF(ISERROR(SUM(SUM(INDIRECT("AE"&ROW())),(INDIREC T("AF"&ROW())),(INDIRECT("AG"&ROW())))/(SUM(COLUMNS((INDIRECT("D"&ROW()):(INDIRECT("AA"&R OW()))))-(COUNTIF(INDIRECT("D"&ROW()):(INDIRECT("AA"&ROW()) ),"N/A"))))),"",SUM(SUM(INDIRECT("AE"&ROW())),(INDIRECT ("AF"&ROW())),(INDIRECT("AG"&ROW())))/(SUM(COLUMNS((INDIRECT("D"&ROW()):(INDIRECT("AA"&R OW()))))-(COUNTIF(INDIRECT("D"&ROW()):(INDIRECT("AA"&ROW()) ),"N/A"))))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cell returns blank result... | Excel Worksheet Functions | |||
| How to get Blank into a cell as a result of a formula | Excel Worksheet Functions | |||
| How to get a formula result zero as blank cell | Excel Discussion (Misc queries) | |||
| Why Am I getting a blank in the result cell? | Excel Discussion (Misc queries) | |||
| How do I make a blank cell with a date format blank? | Excel Worksheet Functions | |||
Linear Mode
