Need a shorter # of Levels
in [General]
|
Prev: protect cell format
Next: Diff in business days
From: Cerealkiller via OfficeKB.com on 3 May 2010 16:07 I have Excel 2000 so these problems are more than 7 levels is there a way to make them shorter. I did make a vlookup for them but if I have to move the cell then I have to re-reference that vlookup so I figured a formula would be easier if it is possible. Thank you for any help that you can provide. =IF(E4="",0,IF((E4="F"),8,IF((E4="D"),4,IF((E4="T"),2,IF((E4="S"),1,IF( (E4="M"),0,IF((E4="L"),-1,IF((E4="H"),-2,IF((E4="G"),-4,IF((E4="C"),-8,"ERR")) )))))))) =IF(E4="",0,IF((E4="F"),8,IF((E4="D"),4,IF((E4="T"),2,IF((E4="S"),1,IF( (E4="M"),0,IF((E4="L"),1,IF((E4="H"),2,IF((E4="G"),4,IF((E4="C"),8,"ERR")))))) )))) -- Message posted via http://www.officekb.com
From: Fred Smith on 3 May 2010 16:10 What was wrong with all the responses to your previous posts? There were several suggested improvements shown. Regards, Fred "Cerealkiller via OfficeKB.com" <u59737(a)uwe> wrote in message news:a77766d2786c2(a)uwe... >I have Excel 2000 so these problems are more than 7 levels is there a way >to > make them shorter. I did make a vlookup for them but if I have to move the > cell then I have to re-reference that vlookup so I figured a formula would > be > easier if it is possible. Thank you for any help that you can provide. > > =IF(E4="",0,IF((E4="F"),8,IF((E4="D"),4,IF((E4="T"),2,IF((E4="S"),1,IF( > (E4="M"),0,IF((E4="L"),-1,IF((E4="H"),-2,IF((E4="G"),-4,IF((E4="C"),-8,"ERR")) > )))))))) > > =IF(E4="",0,IF((E4="F"),8,IF((E4="D"),4,IF((E4="T"),2,IF((E4="S"),1,IF( > (E4="M"),0,IF((E4="L"),1,IF((E4="H"),2,IF((E4="G"),4,IF((E4="C"),8,"ERR")))))) > )))) > > -- > Message posted via http://www.officekb.com >
From: Cerealkiller via OfficeKB.com on 3 May 2010 16:58 These are differnt statements. These have mutiable numbers that they reference and not two cells. So maybe the other would work but I am not sure on how to make them work. Fred Smith wrote: >What was wrong with all the responses to your previous posts? There were >several suggested improvements shown. > >Regards, >Fred > >>I have Excel 2000 so these problems are more than 7 levels is there a way >>to >[quoted text clipped - 10 lines] >> (E4="M"),0,IF((E4="L"),1,IF((E4="H"),2,IF((E4="G"),4,IF((E4="C"),8,"ERR")))))) >> )))) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/201005/1
From: Cerealkiller via OfficeKB.com on 3 May 2010 17:00 Apperantly I just recived this message from Ron. So it looks like a vlookup is the only way but thank you for your response. Vlookup will be a better solution for this. Just reference the table as an absolute reference, or NAME it. --ron Fred Smith wrote: >What was wrong with all the responses to your previous posts? There were >several suggested improvements shown. > >Regards, >Fred > >>I have Excel 2000 so these problems are more than 7 levels is there a way >>to >[quoted text clipped - 10 lines] >> (E4="M"),0,IF((E4="L"),1,IF((E4="H"),2,IF((E4="G"),4,IF((E4="C"),8,"ERR")))))) >> )))) -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/ms-excel/201005/1
From: Kevryl on 4 May 2010 20:52
Hi, just a tip: tho' not so applicable for this situation (as you've said, Vlookup will do it) often splitting a complex multi-level formula between "tests" in hidden columns is simpler. Built-in "spare" hidden columns are also very useful later for expanding a spreadsheet, especially if you have macros that work along rows collecting data. Cheers "Cerealkiller via OfficeKB.com" wrote: > I have Excel 2000 so these problems are more than 7 levels is there a way to > make them shorter. I did make a vlookup for them but if I have to move the > cell then I have to re-reference that vlookup so I figured a formula would be > easier if it is possible. Thank you for any help that you can provide. > > =IF(E4="",0,IF((E4="F"),8,IF((E4="D"),4,IF((E4="T"),2,IF((E4="S"),1,IF( > (E4="M"),0,IF((E4="L"),-1,IF((E4="H"),-2,IF((E4="G"),-4,IF((E4="C"),-8,"ERR")) > )))))))) > > =IF(E4="",0,IF((E4="F"),8,IF((E4="D"),4,IF((E4="T"),2,IF((E4="S"),1,IF( > (E4="M"),0,IF((E4="L"),1,IF((E4="H"),2,IF((E4="G"),4,IF((E4="C"),8,"ERR")))))) > )))) > > -- > Message posted via http://www.officekb.com > > . > |