More than one string variable

  • Thread starter Thread starter SunnyB
  • Start date Start date
S

SunnyB

I have a simple BOM worksheet that totals weights based on a product
list and catalog. It works fine except for my string variable. Depending
on which user adds data to the sheet the string value for quantity might
be "One" or "ONE". I have the formula set to recognize the string "One"
but if they enter the string "ONE" it is not recognized.

How can I alter the formula to recognize both variables. The following
is the current formula that I use. Is the solution as simple as adding
an "or" to the (C3="One" or "ONE",1,(C3))))) ?
Can somebody help me with this?

=IF(ISBLANK(F3)," ",(((E3+(F3/12))*I3)*(IF(C3="One",1,(C3)))))


Thanks

§ß©
 
Hi

Try this:

=IF(ISBLANK(F3)," ",(((E3+(F3/12))*I3)*(IF(OR(C3="One",C3="ONE"),1,(C3)))))
 
Hi

Try this:

=IF(ISBLANK(F3)," ",(((E3+(F3/12))*I3)*(IF(OR(C3="One",C3="ONE"),1,(C3)))))
Click to expand...

Thanks Andy,

I just found, by trial and error, that the case of the character doesn't
matter. It returns a true value for "one", "One" or "ONE".
I'll keep the formula for future anyway.

Maybe you can help me with another problem.
I'm trying to use a control character as a literal string.

=IF(ISBLANK(J4)," ",")

What I'm trying to do is check a cell for any value then return a null value
if that cell is empty or a single quotation mark " (inches) if not empty.

Excel keeps trying to auto correct the string into"
=IF(ISBLANK(J4)," "," "") which returns a value of "0" in that cell.

I've tried " " " but it won't accept it.

What is the control character for inserting a control character as a literal
string?

Thanks again

S
§ß©
 
Use two of them!
"""" will return *one* quotation ( " ) mark.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

Hi

Try this:

=IF(ISBLANK(F3),"
Click to expand...
",(((E3+(F3/12))*I3)*(IF(OR(C3="One",C3="ONE"),1,(C3)))))

Thanks Andy,

I just found, by trial and error, that the case of the character doesn't
matter. It returns a true value for "one", "One" or "ONE".
I'll keep the formula for future anyway.

Maybe you can help me with another problem.
I'm trying to use a control character as a literal string.

=IF(ISBLANK(J4)," ",")

What I'm trying to do is check a cell for any value then return a null value
if that cell is empty or a single quotation mark " (inches) if not empty.

Excel keeps trying to auto correct the string into"
=IF(ISBLANK(J4)," "," "") which returns a value of "0" in that cell.

I've tried " " " but it won't accept it.

What is the control character for inserting a control character as a literal
string?

Thanks again

S
§ß©
 
Back
Top