G
Gregory Day
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())),(INDIRECT("AF"&ROW())),(INDIRECT("AG"&ROW())))/(SUM(COLUMNS((INDIRECT("D"&ROW())INDIRECT("AA"&ROW()))))-(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"&ROW()))))-(COUNTIF(INDIRECT("D"&ROW())INDIRECT("AA"&ROW())),"N/A")))))
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())),(INDIRECT("AF"&ROW())),(INDIRECT("AG"&ROW())))/(SUM(COLUMNS((INDIRECT("D"&ROW())INDIRECT("AA"&ROW()))))-(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"&ROW()))))-(COUNTIF(INDIRECT("D"&ROW())INDIRECT("AA"&ROW())),"N/A")))))