Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to handle this logic test
I really am rusty! Could someone please remind me how to enter a
function in col B that will return different strings depending on the content of col A. Like this: Col A Col B ----- ----- x This y That x This x That z Other y That It seems that the IF function has to have only two values, so cannot be used for my purpose. If it matters, I'm still using my ancient Excel 2000. -- Terry, East Grinstead, UK |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to handle this logic test
Hi Terry,
Am Mon, 08 Feb 2016 18:41:33 +0000 schrieb Terry Pinnell: Col A Col B ----- ----- x This y That x This x That z Other y That It seems that the IF function has to have only two values, so cannot be used for my purpose. in your version you can nest a IR function 6 times. Try: =IF(A1="x","This",IF(A1="y","That","Other")) or =VLOOKUP(A1,{"x","This";"y","That";"z","Other"},2, 0) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to handle this logic test
Hi Terry,
Am Mon, 8 Feb 2016 20:00:32 +0100 schrieb Claus Busch: in your version you can nest a IR function 6 times. sorry, two typos: In the version before 2007 you can nest a IF function (and all other functions) 7 times Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to handle this logic test
Claus Busch wrote:
Hi Terry, Am Mon, 08 Feb 2016 18:41:33 +0000 schrieb Terry Pinnell: Col A Col B ----- ----- x This y That x This x That z Other y That It seems that the IF function has to have only two values, so cannot be used for my purpose. in your version you can nest a IR function 6 times. Try: =IF(A1="x","This",IF(A1="y","That","Other")) or =VLOOKUP(A1,{"x","This";"y","That";"z","Other"},2 ,0) Regards Claus B. Thanks for that characteristically fast reply, Claus! Your first one works a treat - luckily I have exactly six options! Haven't quite got my head around the VLOOKUP function yet. But I'll experiment later, as it seems more versatile. Best wishes, -- Terry, East Grinstead, UK |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to handle this logic test
Another suggestion, to obviate need for nested IFs...
Store all possible criteria in a local scope named range with their respective return values, then use a lookup function. Example: DefinedName: "Sheet1!RetVals" RefersTo: $A$1:$C$2 Layout: X | Y | Z This | That | Other DefinedName: "Criteria" RefersTo: $A3 (with B3 selected so it's col-absolute, row-relative) You can hide these 2 rows so your data can be setup normal. Optionally, you could use 2 cols (A:B). Criteria in colA, return values in colB with this formula... =HLOOKUP(RetVals,Criteria,2,False) Now you can have however many Criteria/RetVal pairs as you like all working with 1 simple formula.<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to handle this logic test
Oops! got the args switched around for the lookup.
See correction below... Another suggestion, to obviate need for nested IFs... Store all possible criteria in a local scope named range with their respective return values, then use a lookup function. Example: DefinedName: "Sheet1!RetVals" RefersTo: $A$1:$C$2 Layout: X | Y | Z This | That | Other DefinedName: "Criteria" RefersTo: $A3 (with B3 selected so it's col-absolute, row-relative) You can hide these 2 rows so your data can be setup normal. Optionally, you could use 2 cols (A:B). Criteria in colA, return values in colB with this formula... =HLOOKUP(Criteria,RetVals,2,False) Now you can have however many Criteria/RetVal pairs as you like all working with 1 simple formula.<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to handle this logic test
On Mon, 08 Feb 2016 20:16:38 +0000, Terry Pinnell
wrote: Haven't quite got my head around the VLOOKUP function yet. But I'll experiment later, as it seems more versatile. I use VLOOKUP/HLOOKUP a lot and they are great once you find the handle. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to handle this logic test
GS wrote:
Oops! got the args switched around for the lookup. See correction below... Another suggestion, to obviate need for nested IFs... Store all possible criteria in a local scope named range with their respective return values, then use a lookup function. Example: DefinedName: "Sheet1!RetVals" RefersTo: $A$1:$C$2 Layout: X | Y | Z This | That | Other DefinedName: "Criteria" RefersTo: $A3 (with B3 selected so it's col-absolute, row-relative) You can hide these 2 rows so your data can be setup normal. Optionally, you could use 2 cols (A:B). Criteria in colA, return values in colB with this formula... =HLOOKUP(Criteria,RetVals,2,False) Now you can have however many Criteria/RetVal pairs as you like all working with 1 simple formula.<g Thanks Gary, I'll try that flexible method at next opportunity. -- Terry, East Grinstead, UK |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MORE THAN ONE LOGIC TEST | Excel Worksheet Functions | |||
logic test | Excel Discussion (Misc queries) | |||
logic test | New Users to Excel | |||
logic test | Excel Worksheet Functions | |||
Logic test | Excel Programming |