| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
maximum characters?
I need help with this formula -
I actually need to add two or more "IF" statements - but it won't let me. What do I need to do? -- =IF(C11="BL",Input!B3*C13*C9,IF(C11="BR",Input!B4* C13*C9,IF(C11="EP",Input!B5*C13*C9,IF(C11="ER",Inp ut!B6*C13*C9,IF(C11="FW",Input!B7*C13*C9,IF(C11="H I",Input!B8*C13*C9,IF(C11="NE",Input!B9*C13*C9,IF( C11="PB",Input!B8*C13*C9)))))))) When I go to put another "IF" statement in - it highlights it and says I have an error. I'm assuming this is because I can only use 7 if statements within a forumula. What other option do I have? Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
maximum characters?
Hi BG,
Yes you are right, it is because you cannot use more than 7 if statements If you want to put more than 7 if statements then - 1. Name the cell where you have 7 if statements (say at B3 and named "oldone") 2. Put down the next if statements in the other cell (say at C3 and named "newone") 3. Put down the if statement at D3 as =if(oldone,oldone,newone) Hope this will work for you Thanks Shail B G wrote: I need help with this formula - I actually need to add two or more "IF" statements - but it won't let me. What do I need to do? -- =IF(C11="BL",Input!B3*C13*C9,IF(C11="BR",Input!B4* C13*C9,IF(C11="EP",Input!B5*C13*C9,IF(C11="ER",Inp ut!B6*C13*C9,IF(C11="FW",Input!B7*C13*C9,IF(C11="H I",Input!B8*C13*C9,IF(C11="NE",Input!B9*C13*C9,IF( C11="PB",Input!B8*C13*C9)))))))) When I go to put another "IF" statement in - it highlights it and says I have an error. I'm assuming this is because I can only use 7 if statements within a forumula. What other option do I have? Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
maximum characters?
I am sorry but I do not understand what you are saying. Could you please
elaborate? (Sorry I am not a very experienced user) -- Barb "shail" wrote: Hi BG, Yes you are right, it is because you cannot use more than 7 if statements If you want to put more than 7 if statements then - 1. Name the cell where you have 7 if statements (say at B3 and named "oldone") 2. Put down the next if statements in the other cell (say at C3 and named "newone") 3. Put down the if statement at D3 as =if(oldone,oldone,newone) Hope this will work for you Thanks Shail B G wrote: I need help with this formula - I actually need to add two or more "IF" statements - but it won't let me. What do I need to do? -- =IF(C11="BL",Input!B3*C13*C9,IF(C11="BR",Input!B4* C13*C9,IF(C11="EP",Input!B5*C13*C9,IF(C11="ER",Inp ut!B6*C13*C9,IF(C11="FW",Input!B7*C13*C9,IF(C11="H I",Input!B8*C13*C9,IF(C11="NE",Input!B9*C13*C9,IF( C11="PB",Input!B8*C13*C9)))))))) When I go to put another "IF" statement in - it highlights it and says I have an error. I'm assuming this is because I can only use 7 if statements within a forumula. What other option do I have? Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
maximum characters?
B G wrote: I need help with this formula - I actually need to add two or more "IF" statements - but it won't let me. What do I need to do? -- =IF(C11="BL",Input!B3*C13*C9,IF(C11="BR",Input!B4* C13*C9,IF(C11="EP",Input!B5*C13*C9,IF(C11="ER",Inp ut!B6*C13*C9,IF(C11="FW",Input!B7*C13*C9,IF(C11="H I",Input!B8*C13*C9,IF(C11="NE",Input!B9*C13*C9,IF( C11="PB",Input!B8*C13*C9)))))))) When I go to put another "IF" statement in - it highlights it and says I have an error. I'm assuming this is because I can only use 7 if statements within a forumula. What other option do I have? Thanks Hello, =choose(match(c11,{"BL";"BR";"EP"},),Input!B3*C13* C9,Input!B4*C13*C9,Input!B5*C13*C9) Regards, Bernd |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
maximum characters?
You can actually nest many more than 7 if statements if you use named formulas. The first thing you have to do is take your current formula and copy it from the formula bar. Go to Insert\Name\Define and type in a formula name (I used IFOne) and then paste in your formula in the Refers to: box replacing the cell reference and then click OK. Once that is done, create the formula for all the other IF scenarios that you have and name them in succession (i.e. IFTwo, IFThree etc.) like you did above. Then in the cell you want the formulas to appear enter the following IF statement: IF(IFOne,IFOne,IF(IFTwo,IFTwo,IF(IFThree,IFThree,0 ))) The example above will allow you to nest 21 if statements! -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=565179 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
maximum characters?
Hi BG,
Excelenator had told you the trick in a better way and I hope now you know how to do it. Thanks to Excelenator too. Shail B G wrote: I am sorry but I do not understand what you are saying. Could you please elaborate? (Sorry I am not a very experienced user) -- Barb "shail" wrote: Hi BG, Yes you are right, it is because you cannot use more than 7 if statements If you want to put more than 7 if statements then - 1. Name the cell where you have 7 if statements (say at B3 and named "oldone") 2. Put down the next if statements in the other cell (say at C3 and named "newone") 3. Put down the if statement at D3 as =if(oldone,oldone,newone) Hope this will work for you Thanks Shail B G wrote: I need help with this formula - I actually need to add two or more "IF" statements - but it won't let me. What do I need to do? -- =IF(C11="BL",Input!B3*C13*C9,IF(C11="BR",Input!B4* C13*C9,IF(C11="EP",Input!B5*C13*C9,IF(C11="ER",Inp ut!B6*C13*C9,IF(C11="FW",Input!B7*C13*C9,IF(C11="H I",Input!B8*C13*C9,IF(C11="NE",Input!B9*C13*C9,IF( C11="PB",Input!B8*C13*C9)))))))) When I go to put another "IF" statement in - it highlights it and says I have an error. I'm assuming this is because I can only use 7 if statements within a forumula. What other option do I have? Thanks |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
maximum characters?
I am not having much luck with that - I'm wondering if it would be easier to
do something like this 4 of the Cells in column B are = to $34, could I do a formula that basically says: If cell C11=BR or FW or NE or HI, then multiply cell B3*C13*C9. This would elimiate the need for more than 7 IF statements, as 4 of the 10 would be included in the statement above. I'm getting there, slowly - thanks for your help. -- "Excelenator" wrote: You can actually nest many more than 7 if statements if you use named formulas. The first thing you have to do is take your current formula and copy it from the formula bar. Go to Insert\Name\Define and type in a formula name (I used IFOne) and then paste in your formula in the Refers to: box replacing the cell reference and then click OK. Once that is done, create the formula for all the other IF scenarios that you have and name them in succession (i.e. IFTwo, IFThree etc.) like you did above. Then in the cell you want the formulas to appear enter the following IF statement: IF(IFOne,IFOne,IF(IFTwo,IFTwo,IF(IFThree,IFThree,0 ))) The example above will allow you to nest 21 if statements! -- Excelenator ------------------------------------------------------------------------ Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768 View this thread: http://www.excelforum.com/showthread...hreadid=565179 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
maximum characters?
Try this
=CHOOSE(LOOKUP(C11,{"BL","BR","EP","ER","FW","HI", "NE","PB"},{1,2,3,4,5,6,7,8}),Input!B3*C13*C9,Inpu t!B4*C13*C9,Input!B5*C13*C9,Input!B6*C13*C9,Input! B7*C13*C9,Input!B8*C13*C9,Input!B9*C13*C9,Input!B1 0*C13*C9) Make sure you take out any line feeds from cut/paste if you do so. I looks in C11 (LOOKUP) to find the two letters you're looking for, then chooses (CHOOSE) the calcuation based on what it finds. HTH Beege "B G" wrote in message ... I need help with this formula - I actually need to add two or more "IF" statements - but it won't let me. What do I need to do? -- =IF(C11="BL",Input!B3*C13*C9,IF(C11="BR",Input!B4* C13*C9,IF(C11="EP",Input!B5*C13*C9,IF(C11="ER",Inp ut!B6*C13*C9,IF(C11="FW",Input!B7*C13*C9,IF(C11="H I",Input!B8*C13*C9,IF(C11="NE",Input!B9*C13*C9,IF( C11="PB",Input!B8*C13*C9)))))))) When I go to put another "IF" statement in - it highlights it and says I have an error. I'm assuming this is because I can only use 7 if statements within a forumula. What other option do I have? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| maximum number of characters within a forumla? | Excel Discussion (Misc queries) | |||
| How do you adjust the maximum characters in a excel header? | Excel Discussion (Misc queries) | |||
| Is there maximum number of characters per cell? | Excel Discussion (Misc queries) | |||
| Maximum amount of characters in a footer | Excel Discussion (Misc queries) | |||
| Maximum number of characters in a cell | Excel Discussion (Misc queries) | |||
Linear Mode
