I am designing a spreadsheet to monitor progress through levels in the
English National curriculum. Children are given a level at the end of
the year from w, 1c,1b,1a,2c,2b,2a etc up to 5a.

I am trying to convert the text eg "2a" into a value ie 7. I have
managed to do this using the IF function however when i get up to a
certain number of if statements it seems to stop excepting any more. Is
there a limit and if so is there a way around it.

The formula I am using is as shown below.

=IF(S5="w",1,(IF(S5="1c",2,(IF(S5="1b",3,(IF(S5="1 a",4,(IF(S5="2c",5,(IF(S5="2b",6,(IF(S5="2a",7,(IF (S5="3c",8,"error")))))))))))))))

I want the formula to continue from where it say "error" to
(if(s5="3b",9,(if(S5="3a",10,(if(s5="4c",11,(..... ....... etc, etc

Where it says "error" at the end the sequence should continue all the
way to "5a"

I am new to using excel in this way and any help would be much
appreciated.


--
timhiley
------------------------------------------------------------------------
timhiley's Profile: http://www.excelforum.com/member.php...o&userid=29534
View this thread: http://www.excelforum.com/showthread...hreadid=492326

One approach is to use a VLOOKUP table. See:

http://www.contextures.com/xlFunctions02.html


--
Gary''s Student


"timhiley" wrote:


I am designing a spreadsheet to monitor progress through levels in the
English National curriculum. Children are given a level at the end of
the year from w, 1c,1b,1a,2c,2b,2a etc up to 5a.

I am trying to convert the text eg "2a" into a value ie 7. I have
managed to do this using the IF function however when i get up to a
certain number of if statements it seems to stop excepting any more. Is
there a limit and if so is there a way around it.

The formula I am using is as shown below.

=IF(S5="w",1,(IF(S5="1c",2,(IF(S5="1b",3,(IF(S5="1 a",4,(IF(S5="2c",5,(IF(S5="2b",6,(IF(S5="2a",7,(IF (S5="3c",8,"error")))))))))))))))

I want the formula to continue from where it say "error" to
(if(s5="3b",9,(if(S5="3a",10,(if(s5="4c",11,(..... ....... etc, etc

Where it says "error" at the end the sequence should continue all the
way to "5a"

I am new to using excel in this way and any help would be much
appreciated.


--
timhiley
------------------------------------------------------------------------
timhiley's Profile: http://www.excelforum.com/member.php...o&userid=29534
View this thread: http://www.excelforum.com/showthread...hreadid=492326

thanks very much, this solves my problem!


--
timhiley
------------------------------------------------------------------------
timhiley's Profile: http://www.excelforum.com/member.php...o&userid=29534
View this thread: http://www.excelforum.com/showthread...hreadid=492326