MrExcel Publishing
Your One Stop for Excel Tips & Solutions

I can't output text from an if statement when many programs are in one field


Posted by Kyle on August 20, 2001 3:21 PM

I am working on a reference template for the company I work for, and I need one of the fields to output a letter according to the number entered in another field. I can get this to work if I only have one if statement in a field, so I know I've got the format correct, and I am using the exact same format for other fields that return a simple number, which have more IF statements in it that the field I have this problem with. Currently, I use the CHAR(##) function, with CHAR(65) being the numerical representation for A, and so on. I have also tried the TEXT function, but to no avail. I would deeply apprectiate any suggestions.


Posted by Kyle on August 20, 2001 6:24 PM

Example of functional and disfunctional programs, still need help!

This is the one that works...

=IF(E4=8,147479)+IF(E4=16,147179)+IF(E4=24,147479)+IF(E4=32,147479)+IF(E4=40,147479)+IF(E4=48,147479)+IF(E4=56,147479)+IF(E4=64,147479)+IF(E4=72,147479)+IF(E4=80,147479)+IF(E4=88,147479)


This is the one that does not...

=IF(E4=8,CHAR(65)),IF(E4=16,CHAR(66)),IF(E4=24,CHAR(67))+IF(E4=32,CHAR(68))+IF(E4=40,CHAR(69))+IF(E4=48,CHAR(70))+IF(E4=56,CHAR(71))+IF(E4=64,CHAR(72))+IF(E4=72,CHAR(73))+IF(E4=80,CHAR(74))+IF(E4=88,CHAR(75))

Posted by Aladin Akyurek on August 21, 2001 12:06 AM

What is...

147479? Your formula will also not "work" if E4=0, 1, or 100. What are you trying to accomplish?

Posted by Kyle on August 21, 2001 3:30 PM

Re: What is...

147479 is just an overhead number for some parts our company makes here, and is further defined by using a "-" and then a sequence of one or two letters. I have an error message set up in case anyone enters any value not within the desired range. what I would actually like to do, is to be able to read E4 and output the necessary suffix for the entered value.

Posted by Aladin Akyurek on August 21, 2001 4:18 PM

Re: What is...

Ok, Kyle. Now I understand (I think).

This could be what you're after:

=IF(AND(MOD(E4,8)=0,E4<=88),147179&"-"&VLOOKUP(E4,{8,"A";16,"B";24,"C";32,"D";40,"E";48,"F";56,"G";64,"H";72,"I";80,"J";88,"K"},2,0),"")

You can put 147179 in some cell, say, A1 and use A1 instead in the above formula.

Aladin