If Statement

P

ppidgursky

I am looking to add more items to a IF statement. The current is:=IF('Week
2'!$E$20="Annual","A",IF('Week 2'!$E$20="Sick","S",IF('Week
2'!$E$20="Personal","PL",IF('Week 2'!$E$20="FLSA Comp","F",IF('Week
2'!$E$20="GA Comp","G",IF('Week 2'!$E$20="Holiday","H",IF('Week
2'!$E$20="Pass","P",IF('Week 2'!$E$20="Leave W/O Pay","AD",""))))))))

I know excel says you can not use more than 7 functions in function. The
above function has 8 levels, and I need to add two more.
The current looks at a cell on 'Week2' and if the field displays "Annual"
for example, on the 2nd sheet it shows an "A". Using the same fields I want
to add 2 more; "Military Leave" to display as "M", and "Jury Duty" as "D".
I am loosing my patience on this one. Please Help.
 
P

Pete_UK

Basically, you build up a table somewhere on your worksheet, e.g. in X1:Y10
and you just list the choices like this:

Annual A
Sick S
Personal PL
FLSA Comp F
GA Comp G
Holiday H
Pass P
Leave W/O Pay AD

Then you would have one formula along the lines of:

=VLOOKUP('Week 2'!E20,X$1:Y$10,2,0)

and that will return the appropriate letter code.

Hope this helps.

Pete
 
P

ppidgursky

That worked perfectly. But one other thing. If field 'Week 2'!E20 is blank,
can I get it to produce a blank. I tried your example and used X1 and Y1 as
blanks, but it did not work. I guess I could type something there and change
the font color, but that is a cheap work around. Trying to get this right.
 
T

Teethless mama

Try this:

=IF('Week 2'!$E$20="Personal","PL",IF('Week 2'!$E$20="Leave W/O
Pay","AD",LEFT('Week 2'!$E$20)))
 
P

ppidgursky

I tried that, and that worked with the original 8 items, but I need to add 2
more to the list, and excel will not allow it. That hole cant add more than 7
funtions in a function thing. Looking at alternative methods, and the look up
works but I can't leave a blank. I am trying to mix the 2 together, if and
vlookup, and it works, but also will not give a blank, just a #N/A
 
P

ppidgursky

This is what I tried, and still received the error:
=IF('Week 1'!$E$21="Annual","A",IF('Week 1'!$E$21="Sick","S",IF('Week
1'!$E$21="Personal","PL",IF('Week 1'!$E$21="FLSA Comp","F",IF('Week
1'!$E$21="GA Comp","G",IF('Week 1'!$E$21="Holiday","H",IF('Week
1'!$E$21="Pass","P",IF('Week 1'!$E$21="Leave W/O Pay","AD",IF('Week
1'!$E$21="Military","M",IF('Week 1'!$E$21="Jury
Duty","J",LEFT('Week1'!$E$21)))))))))))
 
P

ppidgursky

This is what I wrote, and still received the error. Where am I going wrong?
=IF('Week 1'!$E$21="Annual","A",IF('Week 1'!$E$21="Sick","S",IF('Week
1'!$E$21="Personal","PL",IF('Week 1'!$E$21="FLSA Comp","F",IF('Week
1'!$E$21="GA Comp","G",IF('Week 1'!$E$21="Holiday","H",IF('Week
1'!$E$21="Pass","P",IF('Week 1'!$E$21="Leave W/O Pay","AD",IF('Week
1'!$E$21="Military","M",IF('Week 1'!$E$21="Jury
Duty","J",LEFT('Week1'!$E$21)))))))))))
 
P

ppidgursky

I have tried to combined the vlookup and the if statment to a blank. ie:
=IF('Week 1'!E20="","",VLOOKUP('Week 1'!E19,AL9:AM19,2,0))
 
P

ppidgursky

Well i'll be....I have been humbled.....
Now can you explain how that worked for the entire list, when only a few
were listed in the argument.
 
T

Teethless mama

It's "magic" formula


ppidgursky said:
Well i'll be....I have been humbled.....
Now can you explain how that worked for the entire list, when only a few
were listed in the argument.
 
P

ppidgursky

I see that. Thank you. Please forgive my stuburness, you have been extremely
helpful. Does the LEFT automatically use the rest of the list?
 
T

T. Valko

Let's study the table Pete put together:

Annual A
Sick S
Personal PL
FLSA Comp F
GA Comp G
Holiday H
Pass P
Leave W/O Pay AD

=IF('Week 2'!$E$20="Personal","PL",IF('Week 2'!$E$20="Leave W/O
Pay","AD",LEFT('Week 2'!$E$20)))

The formula is using the logic of elimination to arrive at its result.

In the table there are 2 criteria that will return multiple characters,
Personal and Leave W/O Pay. All other criteria return the *first character*
of that criteria. So, instead of testing for each individual criteria the
formula tests for the 2 criteria that will return multiple characters. If
the cell doesn't contain either one of those then logic dictates that it
must** contain one of the other criteria for which we only want the first
character returned. So:

=IF E20 = Personal return PL, else
..... IF E20 = Leave W/O Pay return AD, else
........Return the *first character* of E20

** provided no other entries are possible!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top