Solve : String formula in Excel 2003?

1.	Solve : String formula in Excel 2003?
Answer» Hi My Op syst is Win Xp and Office 2003 I need help with the following cenario To test a a cell and return a VALUE if true by using IF, ELSE statements is LIMITING TO 7 IF,ELSE STATEMENTS and values tested can be either numerical and/or text, ie. If(A6="LHR"),"HEATHROW",IF?(A6="ATL"),"ATLANTA",IF ... I do use this method which allows up to 250 statements in a single string =(A6=>2)(RETURN A NUMERICAL SATEMENT)+(... This is very effeftive, but i need to test and return "TEXT" as below .... =(A6=LHR)(HEATHROW)+(A6=ATL)(ATLANTA)+(... Is this possible and can somebody help A B C 1 LHR HEATHROW 2 ATL ATLANTA 3 HRE HARARE 4 LUN LUSAKA 5 YYC CALGARY Can you please explain in more details what exactly you are TRYING to achieve? This formula: Code: [Select]If(A6="LHR"),"HEATHROW",IF?(A6="ATL"),"ATLANTA",IF ... will display "HEATHROW" or "ATLANTA" etc depending on the text in column A. I do not understand the following statements/formulae. Quote =(A6=>2)(RETURN A NUMERICAL SATEMENT)+(... This is very effeftive, but i need to test and return "TEXT" as below .... =(A6=LHR)(HEATHROW)+(A6=ATL)(ATLANTA)+(... Many thks for yr interest, hope this will shed more light on what i am trying to achieve. By ENTERING a value in cell a1, for example "ATL" then i want to display in cell b1 the full name of the entered abbreviation i.e. "Atlanta" When you use the IF, Else Formulae statements this is a breeze as IF,Else statements can display/return either a numerical value/calculation or text (as in my requirement, but this string is limited to seven enclosed if,else statements My situation calls for (14) fourteen different abbreviations that need to return a full name, so I cannot use the if,else method another way of doing this allowing up to 250 scenarios is showed/explained in the below example instead of the standard if,else which will look like this =If(A1="a",1,If(A1="b",2,if(A1="c",3,if .... will only allow (7) seven ifs in one string, but this method =(A1="a")(1)+(A1="b")(2)+(A1="c")(3)+(A1="d")(4) where "="represents the same as "if" and the""represents "then" if true or the "+" represents "else" .... caters for a total of 250 statements in one string. However my problem is that instead of displaying/returning a numerical number ... 1,2,3,4, as shown, i need to display/return text or a name like this ... =(A1="atl")(Atlanta)+(A1="lhr")(Heathrow)+ ..... but the statement display/return a ... #VALUE! error because of the text"atlanta" Can i, and how, do i get this to work I believe the VLookup Function is a solution for you. Here's a reference: http://www.contextures.com/xlFunctions02.html. Many others can be found by doing a Google search on vlookup function in excel*.

Answer»

Hi
My Op syst is Win Xp and Office 2003
I need help with the following cenario

To test a a cell and return a VALUE if true by using IF, ELSE statements is LIMITING TO 7 IF,ELSE STATEMENTS and values tested can be either numerical and/or text, ie. If(A6="LHR"),"HEATHROW",IF?(A6="ATL"),"ATLANTA",IF ...
I do use this method which allows up to 250 statements in a single string =(A6=>2)*(RETURN A NUMERICAL SATEMENT)+(...

This is very effeftive, but i need to test and return "TEXT" as below .... =(A6=LHR)*(HEATHROW)+(A6=ATL)*(ATLANTA)+(...

Is this possible and can somebody help

A B C
1 LHR HEATHROW
2 ATL ATLANTA
3 HRE HARARE
4 LUN LUSAKA
5 YYC CALGARY

Can you please explain in more details what exactly you are TRYING to achieve?

This formula:
Code: [Select]If(A6="LHR"),"HEATHROW",IF?(A6="ATL"),"ATLANTA",IF ...
will display "HEATHROW" or "ATLANTA" etc depending on the text in column A.

I do not understand the following statements/formulae.

Quote

=(A6=>2)*(RETURN A NUMERICAL SATEMENT)+(...

This is very effeftive, but i need to test and return "TEXT" as below .... =(A6=LHR)*(HEATHROW)+(A6=ATL)*(ATLANTA)+(...

Many thks for yr interest, hope this will shed more light on what i am trying to achieve.

By ENTERING a value in cell a1, for example "ATL" then i want to display in cell b1 the full name of the entered abbreviation i.e. "Atlanta"

When you use the IF, Else Formulae statements this is a breeze as IF,Else statements can display/return either a numerical value/calculation or text (as in my requirement, but this string is limited to seven enclosed if,else statements

My situation calls for (14) fourteen different abbreviations that need to return a full name, so I cannot use the if,else method

another way of doing this allowing up to 250 scenarios is showed/explained in the below example

instead of the standard if,else which will look like this =If(A1="a",1,If(A1="b",2,if(A1="c",3,if .... will only allow (7) seven ifs in one string,
but this method =(A1="a")*(1)+(A1="b")*(2)+(A1="c")*(3)+(A1="d")*(4) where "="represents the same as "if" and the"*"represents "then" if true or the "+" represents "else" ....
caters for a total of 250 statements in one string. However my problem is that instead of displaying/returning a numerical number ... 1,2,3,4, as shown, i need to display/return text or a name like this ... =(A1="atl")*(Atlanta)+(A1="lhr")*(Heathrow)+ ..... but the statement display/return a ... #VALUE! error because of the text"atlanta"

Can i, and how, do i get this to work

I believe the VLookup Function is a solution for you. Here's a reference: http://www.contextures.com/xlFunctions02.html. Many others can be found by doing a Google search on vlookup function in excel.

Solve : String formula in Excel 2003?

Discussion

No Comment Found

Related InterviewSolutions

Reply to Comment