if formulas

S

Sue

I am having problems with how to write this formula, any suggestions?
if F2 =3 then A2 times 250, if F2 = 2, then A2 times 50
 
S

Sue

That works except my data in Cell F2 is coming in as text from data source,
is there a way in the formula to write it so it is reading the text instead
of number. I know I can write a macro to convert to numbers, but is there
an easy way to do in formula??
 
M

Mike Rogers

Sue

Put what is in TEXT in doulble quotes and it will work!

=A2*((F2="3")*250+(F2="2")*50)

Mike Rogers
 
S

Sue

Thanks so much, you saved me hours of work.
Mike Rogers said:
Sue

Put what is in TEXT in doulble quotes and it will work!

=A2*((F2="3")*250+(F2="2")*50)

Mike Rogers
Click to expand...
 
J

Joel

Another way is to change the number to text

=A2*((value(F2)=3)*250+(value(F2)=2)*50)

The solution that make gave works only if you are looking for the numbers to
exactly equal a value. If you needed a number greater or less then you need
my solution

for example
=if(value(F2)>=3,true,false)
 
S

Sue

ok, 1 last question, when the value in F3 is blank it returns a value of
false, I would like it to show 0 or blank.
This is the formula I entered but it errors out.
=IF(iserror(if(F3="02",A3*250,IF(F3="03",A3*250)),0,=IF(F3="02",A3*250,IF(F3="03",A3*250))
=IF(F3="02",A3*250,IF(F3="03",A3*250))
 
J

Joel

Your IF have a true and false response. You left the false results blank
giving the False. I put zero in the formula to give a zero results.

=IF(iserror(if(F3="02",A3*250,IF(F3="03",A3*250,0)),0,=IF(F3="02",A3*250,IF(F3="03",A3*250,0))

=IF(F3="02",A3*250,IF(F3="03",A3*250,0))
 
T

T. Valko

Your formula seems to be an OR condition:

If F3 is either 02 or 03 then multiply A3 by 250.

Try this:

=IF(F3="","",IF(OR(F3={"02","03"}),A3*250,""))
 
T

T. Valko

=IF(F3="","",IF(OR(F3={"02","03"}),A3*250,""))

That can be reduced to:

=IF(OR(F3={"02","03"}),A3*250,"")
 
S

Sue

Thanks so much, if any of you are in Dallas, Tx Area or St Louis Area let me
know I owe you dinner.
 
Top