MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula problem

Posted by Celeste on June 21, 2001 7:25 AM

This is in cell B11: =IF((Legall_Invoice_TN!B11+'P&S_Invoice_TN'!B11)=0," ",(Legall_Invoice_TN!B11+'P&S_Invoice_TN'!B11))

This is in cell D11: =IF(VALUE(B11)=" "," ", C11*VALUE(B11))

The problem is I can't get D11 to work it gives me the #VALUE! error. I want D11 to extend out the value of Bll*C11, if in fact there is a quantity, but D11 has problems because of the formula existing in B11....Help

Posted by Ben O. on June 21, 2001 7:34 AM

Your equation in B11 will result in a space if your first condition is met. When you use the VALUE function on a space, you get a #VALUE! error, because you're asking it for the numeric value of a space.

In D11, try using =IF(B11=" "," ",C11*VALUE(B11)). You also might want to get away from putting spaces (" ") in formulas because they're easy to confuse with null values. You might want to use just null ("") instead.