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
OzGrid Business Applications
Posted by Aladin Akyurek on April 05, 2001 5:53 PM
Try:
=IF(ISNUMBER(B1),IF(B1>0,A1*B1,IF(B1=0,"FREE","")),"")
Aladin
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 ???
ALADIN:
Thanks again for the formula. I used it and also used it for another section in my spreadsheet with modifications. The only other stipulation that I would like to add to the formula below is if c1="x", then b1="", otherwise follow all previous conditions in formula. c1 can only be "x" or empty using validation.
=IF(ISNUMBER(a1),IF(a1<0,"",IF(a1>0.01,"x","")),"")
The above formula is in cell b1 originally looking at a1, but i also want it to consider c1 as described above.
Thank you again for your assistance. I have learned a lot on my own, but also from reading the many other postings on this message board.
