Solved: Excel nested if function--How to use "and" and "or" together

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

k9scooter

Thread Starter
Joined
Nov 24, 2009
Messages
8
I'm creating something that I need a nested function to return to a cell a zone to a map page that is entered. The operator enters a map page in one cell and a different cell displays a specific zone. When I create the formula for the various map pages using "OR" qualifiers one at a time, I overload excel and it says I have too many arguments. I'd like to be able to use a combination of "OR" qualifiers and "AND" qualifiers to enter the various map pages--like this....

If map page 433, 434, 453-460 are input, then "west side" would come out in a different cell.

I've done this:
=IF(OR(C16="433",C16="434",AND(C16=>"453",C16<="460"),L1),IF(OR(C16="403",C16="404"),L2,IF(OR(C16="503",C16="504"),L3))

and I can't quite get it to work.

I can break it up and get the pieces to work independently but not together. if you can help I'd appreciate it.
Thanks
 
Joined
Dec 19, 2008
Messages
744
Welcome to the forum k9scooter.

not sure what you're trying to achive here but And with Or in the same conditrion to test is gonna give you complications, better to separate them is return the same result if true.

try this...

=IF(OR(C16=433,C16=434),L1,IF(AND(C16>=453,C16<=460),L1,IF(OR(C16=403,C16=404),L2,IF(OR(C16=503,C16=504),L3,"False"))))

I suspect a vlookup is gonna work better on this problem.
 

k9scooter

Thread Starter
Joined
Nov 24, 2009
Messages
8
Thanks for the quick reply. What I'm trying to do is:

With the entering of an address into a Rand McNally/Thomas Brothers map program a specific map page is listed (out of about 200 total map pages). One then enters that map page into a cell in this excel created form and a specific territory (from a list of 10 predetermined territories) is then displayed in another cell. There are approx. 30 map pages per territory. I've got them all broken down on a large laminated physical map. I'm just having problems getting the code to work in the nested function. I had it working for about 3 territories--listing each map page in an "OR" statement like this:

=IF(OR(C16="503",C16="504",C16="530",C16="531",C16="532",C16="533",C16="534",
C16="560",C16="561",C16="562",C16="563",C16="564",C16="590",C16="591",
C16="592",C16="593",C16="594",C16="630",C16="631",C16="632",C16="633",
C16="634"),L3

Then....excel informed me that I had reached the 30 argument limit. So I thought that if I could limit some of the arguments with <=> parameters I would have enough room.

Any suggestions???
 
Joined
Jul 25, 2004
Messages
5,458
Hi there, and welcome to the board!

I would not recommend bumping the limit you're reaching. It's there for a reason, and part of that reason (IMHO) is to keep things short and simple. Debugging that formula is going to be a nightmare. I would recommend having a separate table and doing some sort of VLOOKUP() type function. If you had a sample file that accurately depicted your data structure we could probably make the formula for you. Yes, I did italicize part of that sentence, because if it doesn't represent your data, then any formula we make will have to be remade again, a PITA. If you can't post a sample of your work, or your actual file itself, please describe it in as much detail as you can - accurately. :)
 

k9scooter

Thread Starter
Joined
Nov 24, 2009
Messages
8
Zack,
Thanks for getting back to me with an answer. Here is the code for what I've done so far. As I got into it further, excel is telling me that I'm trying to use more levels of nesting than are allowed.

=IF(OR(C16="4369",C16="4459",C16="4640",C16="4641"),L1,IF(AND(C16>="4459",C16<="4462"),L1,IF(AND(C16>="4549",C16<="4552"),L1,IF(AND(C16>="476",C16<="482"),L1,IF(AND(C16>="496",C16<="502"),L1,IF(OR(C16="503",C16="504"),L2,IF(AND(C16>="530",C16<="534"),L2,IF(AND(C16>="560",C16<="564"),L2,IF(AND(C16>="590",C16<="594"),L2,IF(AND(C16>="630",C16<="634"),L2)))))

The various numbers are the specific map book pages and the L1, L2, L3 are the territories that I am clumping a block of map book pages into. I'm a total rookie at excel and I can't even believe that I've gotten as far as I have. I'm now on a mission to get this task accomplished. Whatever help you can provide is appreciated.
 

k9scooter

Thread Starter
Joined
Nov 24, 2009
Messages
8
Here is ALL the info for my problem (the numbers represent map pages, and the L1, L2, L3, etc. are the various territories):

4369,4459-4462, 4549-4552, 4640, 4641, 476-482, 496-502 directed to display what is in cell L1.

503-504, 530-534, 560-564, 590-594, 630-634 directed to display what is in cell L2.

535, 536, 565-569, 595-599, 635-640, 675-680, 706-710, 737-740 directed to display what is in cell L3.

671-674, 701-705, 732-735, 762-765, 792-795, 822-825, 853, 854 directed to display what is in cell L4.

736, 766-772, 796-803, 826-833, 857-861, 887, 888, 918 directed to display what is in cell L5.

862-864, 889-894, 919-924, 950-954, 971-973, 992, 993, 1023 directed to display what is in cell L6

542-544, 570-575, 600-605, 641-645, 681-683 directed to display what is in cell L7

684-686, 711-716, 741-746, 773-782, 804-812, 834-842 865-872, 895-902, 926-932, 956-962, 976-982, 996-999 directed to display what is in cell L8

515, 545-547, 576-578, 606-611, 644-651, 687-692, 717-722, 747, 748 directed to display what is in cell L9
 
Joined
Jul 25, 2004
Messages
5,458
Okay, well thank you for posting the information, but I'm not sure what "directed to disaplay what is in cell LXX" means. Do you mean those values will be shown in that respective cell? Can you explain a little more there? Or perhaps post a sample file? Thank you very much for taking the time to post that information, it is very helpful. :)
 

k9scooter

Thread Starter
Joined
Nov 24, 2009
Messages
8
Zack,
Thanks for the help. I reposted my problem as a vlookup problem with a complete cell table that had all the territories along with all the map pages available and got a reply back from bomb21 that solved it. Thanks again for the help.
 
Joined
Jul 25, 2004
Messages
5,458
Great! Glad you got it solved. You really must keep one issue per thread though. This was more or less a colossal waste of my time, since you posted another thread on it. While it may seem good in theory, it's not a very nice thing to do. Some people think they can do it on different boards, because there's a different target audience, same question on different boards should yield twice the results and exposure right? Wrong. A lot of people frequent multiple boards, and when somebody puts in some time on your issues/problems, then only to find out it was solved by somebody else a few days ago, well, let's just say you don't have to be a rocket scientist to figure out that they've wasted their time.

Anyway, one last mention on my soap box, just be considerate of others and keep one issue per thread. Okay, off soap box now. :)

Take care.
 

k9scooter

Thread Starter
Joined
Nov 24, 2009
Messages
8
Sorry about posting my problem under a different title. I didn't even consider the work that you (or anybody else) was putting in to solve my problem. I mistakenly thought that when the problem couldn't be solved with a nested if function that effectively this thread was dead. That's why I posted it requesting help as a vlookup problem. I'm sorry that I wasted your time as I know that it took you away from helping another. To ALL who helped....thank you.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top