# CONDITIONAL FORMULA

Posted by keith on April 05, 2001 5:10 PM

COLUMN A=1 QTY
COLUMN B=\$2 PRICE
COLUMN C=A*B EXTENSION

I ACTUALLY WANT IN COLUMN C TO BE A FORMULA THAT SAYS IF THERE IS TEXT IN COLUMN B AND THE TEXT IS ZERO, RETURN "FREE", OTHERWISE A*B. I PUT IN THE FORMULA =IF(B1=0,"FREE",B1*A1). THIS WORKS BUT I AM SETTING UP AN ORDER FORM WHERE I WANT TO COPY DOWN THE FORMULA AND IF I USE THIS FORMULA AND THERE IS NO ITEM TO ORDER ON THAT LINE IT SAYS "FREE" BECAUSE THE VALUE IS TECHINICALLY 0. I DO NOT WANT IT TO SAY "FREE" BUT REMAIN BLANK IN THAT COLUMN SINCE THERE IS NO ORDER ENTRY ON THAT LINE. THANKS FOR ANY ASSISTANCE.

Posted by Dave Hawley on April 05, 2001 5:50 PM

Hi Keith

Try this:

=IF(B2="","",IF(B2=0,"FREE",A2*B2))

Dave

Posted by Aladin Akyurek on April 05, 2001 5:53 PM

Try:

=IF(ISNUMBER(B1),IF(B1>0,A1*B1,IF(B1=0,"FREE","")),"")

Posted by keith on April 05, 2001 8:24 PM

The formula did work as well as the other one provided by the other person. Thank you to both, but know I have a new problem. It only works when the cells in column B are not currency or accounting format. If it is currency or accounting it is automatically a zero even if it is not shown in the cell and this would then bring up the word "free" still. Is there a way to format the cells to show a \$ before any number entered in column B that would not effect column c to show free if no value was entered.

Posted by keith on April 05, 2001 8:25 PM

The formula did work as well as the other one provided by the other person. Thank you to both, but know I have a new problem. It only works when the cells in column B are not currency or accounting format. If it is currency or accounting it is automatically a zero even if it is not shown in the cell and this would then bring up the word "free" still. Is there a way to format the cells to show a \$ before any number entered in column B that would not effect column c to show free if no value was entered.

Posted by keith on April 05, 2001 8:27 PM

Thanks I figured it out

Thanks I figured it out

Posted by keith on April 05, 2001 8:28 PM

Thanks I figured it out

Thanks I figured it out

Posted by keith on April 05, 2001 9:34 PM

I RECYCLED YOUR FORMULA TO ANOTHER CELL BUT HAVE ???