# Need formula to assign a value in Excel



## texboy (Nov 20, 2003)

Hello all,

I'm able to assign a value from a number to a letter, but not the other way around which is what I need.

I have a data validation (A,B,C,D) and want to assign a value on another column depending on the answer.

example:
A should equal 1
B should equal 2...

Thanks.


----------



## Glaswegian (Sep 16, 2005)

Hi

Do you mean something like

=IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,IF(A1="D",4,""))))


----------



## texboy (Nov 20, 2003)

Glaswegian said:


> Hi
> 
> Do you mean something like
> 
> =IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,IF(A1="D",4,""))))



That's exactly what I needed!

I was using the quotes in the wrong place: =IF(A1=A,"1")

Thanks Glaswegian.


----------



## JanP (Mar 14, 2009)

That works except for the last four letters - W, X, Y, Z. Is this formula restricted to a certain number?


----------



## Glaswegian (Sep 16, 2005)

Hi and welcome.

Please provide some more detail. You can only have 7 nested IF statements in a formula.


----------



## JanP (Mar 14, 2009)

Thank you for the reply. I actually have 22 nested IF clauses (A =1, to V = 22). That must be the limit. So how do I get the last 4 letters? Can I make a complementary IF statement and if so, how?


----------



## Glaswegian (Sep 16, 2005)

What version of Excel are you using?

Can you post the formula (or just part of it) you have at the moment?


----------



## JanP (Mar 14, 2009)

I'm using Office Professional 2007. The formula is =IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,IF(A1="D",4,IF(A1="E",5,IF(A1="F",6,IF(A1="G",7,IF(A1="H",8,IF(A1="I",9,IF(A1="J",10,IF(A1="K",11,IF(A1="L",12,IF(A1="M",13,IF(A1="N",14,IF(A1="O",15,IF(A1="P",16,IF(A1="Q",17,IF(A1="R",18,IF(A1="S",19,IF(A1="T",20,IF(A1="U",21,IF(A1="V",22,ID(A1="W",23,IF(A1="X",24,IF(A1="Y",25,IF(A1="Z",26,))))))))))))))))))))))))))
and it works up to the letter "V" but returns #### for W, X, Y and Z.


----------



## Glaswegian (Sep 16, 2005)

To be honest I don't understand how that works at all. I cannot even input the formula to XL2002 - too many IF statements. The error you describe is normally associated with the column not being wide enough to display the full cell value.


----------



## JanP (Mar 14, 2009)

The task is for my pupils to enter their names, get equivalent numbers according to the place in the alphabet each letter is, and to sum the value of their names. I can set up the formula once and copy for the rest of the column. The nested IF formula returns correct values for letters A:V but bombs out for the last 4 letters. 
I can do a similar exercise using an array and VLOOKUP but that involves having the key and values visible. Using the IF formula the children are "hooked" into the magic which acts as strong motivation to explore using Excel.
Maybe Office 2010 will allow more nesting.

Thanks anyway.


----------



## hems_27 (Aug 14, 2008)

Hi Jan,


You still can use vlookup & array. If you dont want Key & variable visible then you can put your array or list in different sheet & hide that sheet. it will still be same as an 'If statement' and keys will not be visible. 

Best Regards :wink:


----------



## JanP (Mar 14, 2009)

Thanks. I couldn't transfer the data to another sheet but grouping the columns (Data, Group, Outline) hides the data just fine and lets me show the data again in one click. Thanks to all of you who helped me solve this problem.


----------



## Elkar (Mar 17, 2008)

Excel 2007 increased the nesting limit from 7 to 64. It looks like the reason your formula stops working at "V" is because of a typo. You have "ID" instead of "IF".

But, yes, a VLOOKUP with a table is a more efficient way to do this.

HTH
Elkar


----------



## JanP (Mar 14, 2009)

Oh, oh, oh! What a basic error! I guess in such a long repetitive formula it's on the cards there'll be a typing error. Always but always check the detail. Thank you. After making the corrections the formula works. Now I have 2 magic tricks - VLOOKUP with an array and a complicated IF statement.


----------



## Glaswegian (Sep 16, 2005)

Elkar said:


> Excel 2007 increased the nesting limit from 7 to 64.


Thanks for that info - I've never used 2007 so wasn't sure if that was one of the things that had been changed. :wave:


----------

