hi anyone can give me advice about this formula? =IF(BY7>=0;BY7*I7;" ") ----------- it reads from some cells, which may appear with empty result (" "). function below shows error, when reading empty result in it's calculation. normally " " is a good solution, but currently it gives " " when value is less <0, but when read cell value is empty - error. thanks in advance Igal'.

Hmm, I couldn't see any problem. I tested it in Excel 2003 and it works. Is there something additional, I should aware of. Did you get the error on the cell, where the formula is in or is the error related on another cell or function or maybe a diagram? Or perhaps it's one of the features in Office 2007, Microsoft offers the users and you have to wait for a fix of Microsoft.

It might help if you told us what error you are getting. " " is not empty. " " is a space, which is text. A space in I7 or BY7 will result in an #VALUE error since you can't perform an arithmetic operation on a cell that contains text. The real way to create an empty cell with an IF statement is as follows: =IF(BY7>=0;BY7*I7;"") However, if you want to perform an arithmetic operation on the results of that formula, you have to use: =IF(BY7>=0;BY7*I7;0) then use Tools..Options and uncheck the Zero Values option so a cell that equates to zero will appear empty.

Excel interprets empty cells as 0. E.g. A1 (empty) B1=A1 B1 shows 0

re: "Excel interprets empty cells as 0." True, but what was your point as it relates to this question? I'm just curious.

thanks, eventually I decided to change the formula. the problem I see consistently happens in Excel 2007 & 2010 is that some functions fails in long range sells (cells like BB or further) in my case, BY can show an empty sell, while getting formula result as error (it's written in similar way, to ignore errors happening due to math function to an empty cell) normally =IF(COUNT(xxx)=0;" ";xxx) works great, but as I said above - it may stop showing empty cell. in addition, it may not subtract negative values, when similar function used (for example: BY-A10 (A10=-150) ==> it will come empty. rest works fine. is there any other solution, rather than using: ? =IF(COUNT(xxx)=0;" ";xxx) (I need it, because some cells come with error, as results) thanks!

re: "because some cells come with error" I said it before, I'll say it again: It might help if you told us what error you are getting. I said it before, I'll say it again: " " is not an empty cell - it's a space. =IF(COUNT(xxx)=0;" ";xxx) will never, ever result in an empty cell. This formula will either evaluate to a space or to whatever is in xxx. Try this test: Open a new spreadsheet. Put this formula in A1: =IF(C1=0," ",C1) Leave C1 empty Put this formula in B1: =LEN(A1) Put this formula in B2: =LEN(A2) B1 will display 1, B2 will display 0. That's because A2 is empty, but A1 contains a space. In other words, it's not empty.