Formula result as real empty/blank cell
How can I get a formula result to be an actual empty cell (usually when using
the IF function). I know that "" of NA() is an option to get a Blank cell, but this results in errors when the result of this formula is used in another function. Example: IF(isblank(a1);"";A1*B1). If A1 does not contain a value I would like the formula to result in an EMPTY (or Blank) result. Now I have used "" which gives no visible result in the formula cell, but when I multiply this formula result with another value it will result in #VALUE! It seems to be one of the shortcommings of Excel!? Regards JB |
Formula result as real empty/blank cell
"Excelerate-nl" wrote:
How can I get a formula result to be an actual empty cell (usually when using the IF function)... Don't think this is possible Example: IF(isblank(a1);"";A1*B1). If A1 does not contain a value I would like the formula to result in an EMPTY (or Blank) result. Now I have used "" which gives no visible result in the formula cell, but when I multiply this formula result with another value it will result in #VALUE! Instead of using =IF(isblank(a1);"";A1*B1) Perhaps putting it as: =IF(A1="";"";A1*B1) would suffice -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Formula result as real empty/blank cell
In the downstream formulas where you are getting #VALUE errors, either use
ISERR() or ISERROR() just as you used ISNA() in the first one. That will handle those cases for you. "Excelerate-nl" wrote: How can I get a formula result to be an actual empty cell (usually when using the IF function). I know that "" of NA() is an option to get a Blank cell, but this results in errors when the result of this formula is used in another function. Example: IF(isblank(a1);"";A1*B1). If A1 does not contain a value I would like the formula to result in an EMPTY (or Blank) result. Now I have used "" which gives no visible result in the formula cell, but when I multiply this formula result with another value it will result in #VALUE! It seems to be one of the shortcommings of Excel!? Regards JB |
Formula result as real empty/blank cell
Dear JLatham,
Thanks for responding. Your suggestion will solve downstream errors, but when the outcome of the formula relates to a lot of other formulas it is not a nice scenario. I guess it's just not possible to define a result of a formula as a blank cell (would be a good suggestion for Microsoft to have something as BLANK(), similar to NA()) Regards, JB "JLatham" wrote: In the downstream formulas where you are getting #VALUE errors, either use ISERR() or ISERROR() just as you used ISNA() in the first one. That will handle those cases for you. "Excelerate-nl" wrote: How can I get a formula result to be an actual empty cell (usually when using the IF function). I know that "" of NA() is an option to get a Blank cell, but this results in errors when the result of this formula is used in another function. Example: IF(isblank(a1);"";A1*B1). If A1 does not contain a value I would like the formula to result in an EMPTY (or Blank) result. Now I have used "" which gives no visible result in the formula cell, but when I multiply this formula result with another value it will result in #VALUE! It seems to be one of the shortcommings of Excel!? Regards JB |
Formula result as real empty/blank cell
Excelerate-nl wrote: Dear JLatham, Thanks for responding. Your suggestion will solve downstream errors, but when the outcome of the formula relates to a lot of other formulas it is not a nice scenario. I guess it's just not possible to define a result of a formula as a blank cell (would be a good suggestion for Microsoft to have something as BLANK(), similar to NA()) Regards, JB "JLatham" wrote: In the downstream formulas where you are getting #VALUE errors, either use ISERR() or ISERROR() just as you used ISNA() in the first one. That will handle those cases for you. "Excelerate-nl" wrote: How can I get a formula result to be an actual empty cell (usually when using the IF function). I know that "" of NA() is an option to get a Blank cell, but this results in errors when the result of this formula is used in another function. Example: IF(isblank(a1);"";A1*B1). If A1 does not contain a value I would like the formula to result in an EMPTY (or Blank) result. Now I have used "" which gives no visible result in the formula cell, but when I multiply this formula result with another value it will result in #VALUE! It seems to be one of the shortcommings of Excel!? Regards JB |
All times are GMT +1. The time now is 03:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com