Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Only 8 if A5=this, then B4=that. How can I put in more?
I am using the following formula and if there is more than 8, it gives me an error msg each time (and I am sure to add additional )'s at the end. Any suggestions how I could get it to allow as many as I want. Thanks in advance. =IF(F5="1K","51126",IF(F5="2AC","36320",IF(F5="0AP ","49003",IF(F5="3AC","36316",IF(F5="9CH","45284", IF(F5="1DB","318",IF(F5="6AC","53570",IF(F5="149", "1045","")))))))) I am wanting to be able to put as many as I want. Thanks in advance. Mcr1 -- mcr1 ------------------------------------------------------------------------ mcr1's Profile: http://www.excelforum.com/member.php...o&userid=15496 View this thread: http://www.excelforum.com/showthread...hreadid=498860 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Only 8 if A5=this, then B4=that. How can I put in more?
On Fri, 6 Jan 2006 15:14:48 -0600, mcr1
wrote: I am using the following formula and if there is more than 8, it gives me an error msg each time (and I am sure to add additional )'s at the end. Any suggestions how I could get it to allow as many as I want. Thanks in advance. =IF(F5="1K","51126",IF(F5="2AC","36320",IF(F5="0A P","49003",IF(F5="3AC","36316",IF(F5="9CH","45284" ,IF(F5="1DB","318",IF(F5="6AC","53570",IF(F5="149" ,"1045","")))))))) I am wanting to be able to put as many as I want. Thanks in advance. What you're doing is called "nesting", and it has a limit. Specifically: "Nesting level limits: A formula can contain up to seven levels of nested functions." I suggest that instead of doing it that way, you create a table somewhere in your workbook and use VLookups to compare F5's value to that table. It makes for much neater and less error-prone formulas. [F1] help has a pretty good worked example explaining how to use the function if you haven't done so before. --------------------------------------------------------- Hank Scorpio scorpionet who hates spam is at iprimus.com.au (You know what to do.) * Please keep all replies in this Newsgroup. Thanks! * |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Only 8 if A5=this, then B4=that. How can I put in more?
Can't be done with an IF as it only allows for 7 nested if in a formula. What you can do is set up you data somewhere in an out of the way place of your worksheet or workbook then use a VLOOKUP formula. =VLOOKUP(A1,your_table,2,0) HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=498860 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Only 8 if A5=this, then B4=that. How can I put in more?
Excel only allows 7 nested IF statements with no exceptions. There are some
work arounds though with the VLOOKUP functions. The easiest way is to make a table in another sheet and reference that table. It is possible to insert the table in the formula itself like this... =VLOOKUP(F5,{"1K","51126";"2AC","36320";"0AP","490 03";"3AC","36316";"9CH","45284";"1DB","318";"6AC", "53570";"149","1045"},2,0) and if you want the "" part you need something like this... =IF(ISERROR(VLOOKUP(F5,{"1K","51126";"2AC","36320" ;"0AP","49003";"3AC","36316";"9CH","45284";"1DB"," 318";"6AC","53570";"149","1045"},2,0)),"",VLOOKUP( F5,{"1K","51126";"2AC","36320";"0AP","49003";"3AC" ,"36316";"9CH","45284";"1DB","318";"6AC","53570";" 149","1045"},2,0)) Of course if you use a table instead the formula will be a lot cleaner and will look like this =VLOOKUP(F5,A1:B8,2,FALSE) or this to add the "" part =IF(ISERROR(VLOOKUP(F5,A1:B8,2,FALSE)),"",VLOOKUP( F5,A1:B8,2,FALSE)) "mcr1" wrote: I am using the following formula and if there is more than 8, it gives me an error msg each time (and I am sure to add additional )'s at the end. Any suggestions how I could get it to allow as many as I want. Thanks in advance. =IF(F5="1K","51126",IF(F5="2AC","36320",IF(F5="0AP ","49003",IF(F5="3AC","36316",IF(F5="9CH","45284", IF(F5="1DB","318",IF(F5="6AC","53570",IF(F5="149", "1045","")))))))) I am wanting to be able to put as many as I want. Thanks in advance. Mcr1 -- mcr1 ------------------------------------------------------------------------ mcr1's Profile: http://www.excelforum.com/member.php...o&userid=15496 View this thread: http://www.excelforum.com/showthread...hreadid=498860 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Only 8 if A5=this, then B4=that. How can I put in more?
Never done that. I have no idea how to make a table. Any suggestions? I cant even figure it out with the help function. Thanks again -- mcr1 ------------------------------------------------------------------------ mcr1's Profile: http://www.excelforum.com/member.php...o&userid=15496 View this thread: http://www.excelforum.com/showthread...hreadid=498860 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Only 8 if A5=this, then B4=that. How can I put in more?
Find a blank spot somewhere in your worksheet, col (1) put your code, col (2) put the corresponding number. i.e Col(1)-Col(2) 1k - 51126 2Ac - 36320 OAP - 49003 etc..... then select your table, hit CTRL+F3, in the "Names in workbook" text box type a name for your table....say table1 and hit OK. Now for your formula use: =VLOOKUP(F5,table1,2,0) Good Luck JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=498860 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Only 8 if A5=this, then B4=that. How can I put in more?
On Fri, 6 Jan 2006 17:37:03 -0600, mcr1
wrote: Never done that. I have no idea how to make a table. Any suggestions? I cant even figure it out with the help function. Thanks again The Help makes it about as clear as it can be, but let's try an example that's customised to your own needs and see whether that makes it easier. Start with a blank workbook. Now, on sheet 1, go to cell A1 and enter the text "Cell Value". (Without the quotes) In cell B1, enter "Lookup value". These will be your table headings. (Which aren't really needed, but they make it easier to follow what's going on.) Now format the cells below those as Text. (Select the cells, then go Format - Cells and set the Number Format as Text.) You need to do this because some of your lookup values have leading zeroes. Now enter the following values into the cells specified below: Cell Value A2 1K B2 51126 A3 2AC B3 36320 A4 0AP B4 49003 A5 3AC B5 36316 So you can see that you have the IF condition in column A, and the value that you want in column B right alongside it. This is your lookup table. You can run this lookup table down as far as you want it to go. Now, go to sheet 2 and enter the text "2AC" (again without the quotes) into cell F5. (We've chosen F5 only because it corresponds with your original formula.) Now in any OTHER cell, enter the following formula: =VLOOKUP(F5,Sheet1!$A$1:$B$5,2,FALSE) What you should see is the value 36320; that is, the return value for the code 2AC. The VLookup has 4 arguments, and what they mean is this: WHAT value do you want to look up? Whatever's in cell F5. WHERE do you want to look it up from? The table Sheet1!$A$1:$B$5. WHICH column contains the value that we want to look up? Column 2. DO we want an approximate match? No, which is why the last argument is False. In reality I wouldn't enter the lookup table into the VLookup formula using absolute cell references like this, I'd use a range name. And a dynamic range name at that. However taking it one step at a time, see whether the above example makes it a bit clearer for you. If it does, and you'd like to improve it by using range names, post again and we'll take it from there. --------------------------------------------------------- Hank Scorpio scorpionet who hates spam is at iprimus.com.au (You know what to do.) * Please keep all replies in this Newsgroup. Thanks! * |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Only 8 if A5=this, then B4=that. How can I put in more?
A table is simply two or more columns of data, as opposed to a list
which is just a single column. So a table of your data would look like: 1K 51126 2AC 36320 OAP 49003 3AC 36316 9CH 45284 1DB 318 6AC 53570 149 1045 In Sloth's posting, he was assuming that this table would be typed in cells from A1 to B8, although you could put this table anywhere that is suitable in your worksheet - if you do, you would need to change the range A1:B8 in his formula to suit the cells where your table is located. Using a table like this, your long formula with all the IF statements can be replaced with the VLOOKUP formula he supplied, i.e.: =VLOOKUP(F5,A1:B8,2,FALSE) What this does is to compare the value in cell F5 with the values in the first column of the table, i.e. in column A, and if it finds an exact match then it will return the corresponding value from column 2 of that table, i.e. from column B. This is exactly the same as having 8 IF statements. A further advantage of using Vlookup, though, is that you can have many more entries in your table. Imagine you have 20 values of A and corresponding values in B, occupying A1 to B20 - all you need to do is make a slight amendment to the formula as follows: =VLOOKUP(F5,A1:B20,2,FALSE) and this will accommodate the extra conditions. If the value in F5 does not exist in the column of values in your table, however, then the function will return #N/A to indicate this error. Sloth's final formula helps you to prevent this from happening - basically it means "If the lookup function will return an error, then just put "" in the cell, otherwise let the lookup function return its value". Hope this helps to explain things a bit more. Pete |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Only 8 if A5=this, then B4=that. How can I put in more?
I sure do appreciate all of the help. Especially Pete and Hank. You guys just laid it out there and really took the time to help when you did not have to; you did it cause that is the kind of guys you are, and I really do appreciate that. Thank you. It's working out well, except for the #N/A. I cant seem to get rid of that. I am using the exact formula that was posted he =VLOOKUP(F5,Sheet1!$A$2:$B$5,2,FALSE) Any final suggestions would be appreciated. Thanks again for helping a fella out. -- mcr1 ------------------------------------------------------------------------ mcr1's Profile: http://www.excelforum.com/member.php...o&userid=15496 View this thread: http://www.excelforum.com/showthread...hreadid=498860 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Only 8 if A5=this, then B4=that. How can I put in more?
Judging by your if formula you should have at least 8 rows of data in your table and your formula is looking at only 5 A2:B5, make sure that the formula refers to the correct location of your table. The easiest way to assure that is when your typing your formula, instead of typing that part of your formula simply locate your table and select it, excel will put the correct range in the formula. HTH JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=498860 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Only 8 if A5=this, then B4=that. How can I put in more?
On Fri, 6 Jan 2006 21:20:11 -0600, mcr1
wrote: I sure do appreciate all of the help. Especially Pete and Hank. You guys just laid it out there and really took the time to help when you did not have to; you did it cause that is the kind of guys you are, and I really do appreciate that. Thank you. You're welcome. It's working out well, except for the #N/A. I cant seem to get rid of that. I am using the exact formula that was posted he =VLOOKUP(F5,Sheet1!$A$2:$B$5,2,FALSE) OK, I didn't cover N/A errors in my first post because I thought it more important that you get a handle on the structure of the table, and on using the VLookup function. Pete did cover it in in his post to some extent. You'll get the N/A error if the value that you're looking up (that is, the first argument of the Vlookup formula) doesn't appear in the first column of the table. There could be a number of reasons for this. - The first is (obviously) that the value that you're looking up just doesn't appear in the first column. - The second is if the value that you're looking up is stored as a number in your table, but you're looking it up as text or vice versa. (That could be a bit confusing. Let me explain it this way; if the formula was, say, =VLOOKUP(75,Sheet1!$A$1:$B$7,2,FALSE), then the NUMBER 75 must appear somewhere in the first column of the lookup table. If instead you have 75 *formatted as text* in that table, VLookup won't find it and will return N/A. Conversely, if you have the function as =VLOOKUP("75",Sheet1!$A$1:$B$7,2,FALSE) (note the double quotes around the 75 this time) and you have 75 formatted as a NUMBER in the lookup table, again, Vlookup won't find it. I don't think this applies in your case (so far) because all of your first column entries appear to be alphanumeric (that is, they have both text AND numbers), so they would have to be formatted as text. I'm only mentioning it in case you come across this problem in the future.) - The third possibility, which was mentioned by JG/Pinmaster, is if the row containing the lookup value falls OUTSIDE the range that you've specified as the table. I listed only the first 5 of your entries and then said that you could spread it down as far as you needed to. Pete was less lazy than me and specified all of the combinations that you had mentioned in your original post. But in any case, you DO need to make sure that the range name $A$2:$B$5 is changed as needed to specify the WHOLE list. If you have 7 items, it'll be $A$2:$B$8. If you have 10, it'll be $A$2:$B$11 and so on. (In fact I'd usually start from row 1, not 2, even if the first row contains headings only. That way if you insert a new row at the top of the table (that is, in row 2), the VLookup formulas will automatically change to include the new row.) What people normally do when using a VLookup is to use an IsError or IsNa function as well to prevent the N/A error from appearing. For example: =IF(ISNA(VLOOKUP(F5,Sheet1!$A$1:$B$7,2,FALSE)),"No t Found", VLOOKUP(F5,Sheet1!$A$1:$B$7,2,FALSE)) That way it will show a more informative error message if, for whatever reason, the value isn't there. Any final suggestions would be appreciated. Thanks again for helping a fella out. --------------------------------------------------------- Hank Scorpio scorpionet who hates spam is at iprimus.com.au (You know what to do.) * Please keep all replies in this Newsgroup. Thanks! * |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|