Assigning value to text

[Robincambs]

I am looking to assign a value to a text string and then insert the
corresponding value into a cell

As follows:

A1 to A100 could equal "NO CHANGE" or "FOR CHANGE"
If A1 = "NO CHANGE" then I1 = "10" else if A1 = "FOR CHANGE" then I1 =
"20"
If A2 = "NO CHANGE" then I2 = "10" else if A2 = "FOR CHANGE" then I2 =
"20"

then this to loop until A100

any help will be gratefully received

 

[Rick Rothstein]

Give this a try...

=10+10*(A1="FOR CHANGE")

and copy it down as needed.

 

[Stefi]

Enter
=IF(A1="NO CHANGE",10,20)
and drag it down to A100.

Regards,
Stefi

„Robincambs†ezt írta:

 

[Robincambs]

Give this a try...

=10+10*(A1="FOR CHANGE")

and copy it down as needed.

--
Rick (MVP - Excel)










- Show quoted text -

Thanks for the suggestion, it doesn't do what i was hoping. There are
2 possible entries of text "NO CHANGE" or "FOR CHANGE" the suggestion
you made only provives a result of 10

Thanks
Rob

 

[Rick Rothstein]

What I posted will return 20 if A1 contains "FOR CHANGE" and will return 10
otherwise. Perhaps you mean it returns 10 if A1 is blank? Try this then...

=IF(A1="","",10+10*(A1="FOR CHANGE"))

 

[Robincambs]

Enter
=IF(A1="NO CHANGE",10,20)
and drag it down to A100.

Regards,
Stefi

„Robincambs” ezt írta:








- Show quoted text -

Unfortunatly neither of those work - perhaps its due to me using
conditional formatting or dropdown lists?

 

[Robincambs]

What I posted will return 20 if A1 contains "FOR CHANGE" and will return 10
otherwise. Perhaps you mean it returns 10 if A1 is blank? Try this then...

=IF(A1="","",10+10*(A1="FOR CHANGE"))

--
Rick (MVP - Excel)








- Show quoted text -

Yes - that works great, how could i address 3 different conditions
such as NO CHANGE" or "FOR CHANGE" or "PENDING CHANGE"

 

[Stefi]

Unfortunatly neither of those work - perhaps its due to me using
conditional formatting or dropdown lists?

I don't think so, something else must be the problem, either Rick's solution
or mine should work, maybe your data contain some factor (case, redundant
spaces, etc.) Please post sample data!

Stefi

 

[Rick Rothstein]

It is usually easier if you follow the same posting method as the person who
responded to you... in my case, posting the responses at the top of the
message (that way the text is not bouncing back and forth from top to
bottom).

As for your problem... conditional formatting or dropdown lists should not
affect the formulas that have been posted from working. I am going to guess
that you have more in your cells than NO CHANGE or FOR CHANGE... perhaps
some extra spaces at the front or back?

 

[Robincambs]

Sorry about the user group etiquette, something i'm not farmiliar
with.

I have found your suggestion works fine - it was my formatting that
was at fault.

how could i address 3 different conditions and assign 3 values
such as NO CHANGE" = 10 or "FOR CHANGE" = 20 or "PENDING CHANGE" = 30

Kind regards
Rob

 

[Rick Rothstein]

You should always ask for what you want initially and not simplify your
requests for us... the nature of formula (and even macro) solutions are that
they tend to be good only for the conditions they were designed to handle
and no more. For your current question, which did not tell us what you
wanted for the PENDING CHANGE condition (so I assumed 30 in my sample
formula), try this...

=IF(A1="","",IF(A1="NO CHANGE",10,IF(A1="FOR CHANGE",20,30)))

 

[Robincambs]

Super - thanks Rick, i have learnt a few lessons today...!

 

[Stefi]

=IF(A1="NO CHANGE",10,IF(A1="FOR CHANGE",20,30))
Regards,
Stefi


„Robincambs†ezt írta: