|
7+ if statements
I am trying to create a worksheet that will allocate operating expenses to 6
different departments. I have 14 allocation methods on tab "Alloc" that are identified with letters A thru L. On the expense alloction tab I have assigend a letter(A thru L) in column "E" to identify the allocation method. My formula is: =IF($E7="A",($D7*'Alloc'!E$7),IF($E7="B"($D7*'Allo c'!E$10).....thru "L". However, my formula will only accept the if statments thur letter H ( 7 nested). Is there any way around this that will allow me to include the other 7 allocation methods in my if statments? As you can probably tell I'm pretty basic. Thanks |
7+ if statements
hi,
use 2 if formulas in 2 columns, hide the first. do the first 5 to calculate else zero then do the last 5 picking up if the first was zero. like this MY data in column O ,P and Q, 1st formula in R 2nd in S for developement and test purposes. first 5 =IF($O2="a",$P2*$Q2,IF($O2="b",$P2*$Q3,IF ($O2="c",$P2*$Q4,IF($O2="d",$P2*$Q5,IF ($O2="e",$P2*$Q6,0))))) the formula wrapped. suppose to be one line. notice that is it is not a,b,c,d, or e then zero so it would calculate if it were an a,b,c,d or e. then hide this column second 5 =IF($R2<0,$R2,IF($O2="f",$P2*$Q2,IF($O2="g",$P2*$ Q2,IF ($O2="h",$P2*$Q2,IF($O2="I",$P2*$Q2,$P2*$Q2))))) notice that if the previous formula didn't produce zero ie it calculated a number then put the contents the previous formula but if zero then keep looking for a f,g,h,i or j this way you don't excel the 7 cap and you have 10 if then else. I just tested this to make sure. you will have to change it all for your situation. but it works. hope it helps -----Original Message----- I am trying to create a worksheet that will allocate operating expenses to 6 different departments. I have 14 allocation methods on tab "Alloc" that are identified with letters A thru L. On the expense alloction tab I have assigend a letter(A thru L) in column "E" to identify the allocation method. My formula is: =IF($E7="A",($D7*'Alloc'!E$7),IF($E7="B"($D7*'All oc'! E$10).....thru "L". However, my formula will only accept the if statments thur letter H ( 7 nested). Is there any way around this that will allow me to include the other 7 allocation methods in my if statments? As you can probably tell I'm pretty basic. Thanks . |
7+ if statements
On Fri, 18 Feb 2005 11:41:01 -0800, "JayE"
wrote: I am trying to create a worksheet that will allocate operating expenses to 6 different departments. I have 14 allocation methods on tab "Alloc" that are identified with letters A thru L. On the expense alloction tab I have assigend a letter(A thru L) in column "E" to identify the allocation method. My formula is: =IF($E7="A",($D7*'Alloc'!E$7),IF($E7="B"($D7*'All oc'!E$10).....thru "L". However, my formula will only accept the if statments thur letter H ( 7 nested). Is there any way around this that will allow me to include the other 7 allocation methods in my if statments? As you can probably tell I'm pretty basic. Thanks Take a look at VLOOKUP in HELP. You should probably use VLOOKUP. On your Alloc worksheet, set up a table with A-L in column 1, and your "allocation method" in column 2. Then use a formula of the form: =$D7 * VLOOKUP($E7, 'Alloc'!$A$1:$B$n,2) --ron |
7+ if statements
letter number in the alphabet
a =IF(OR(B2="a",B2="b",B2="c",B2="d",B2="e",B2="f"), IF(B2="a",1,IF(B2="b",2,IF (B2="c",3,IF(B2="d",4,IF(B2="e",5,6))))),IF(OR(B2= "g",B2="h",B2="i",B2="j",B 2="k",B2="l"),IF(B2="g",7,IF(B2="h",8,IF(B2="i",9, IF(B2="j",10,IF(B2="k",11, 12))))),IF(OR(B2="m",B2="n",B2="o",B2="p",B2="q",B 2="r"),IF(B2="m",13,IF(B2= "n",14,IF(B2="o",15,IF(B2="p",16,IF(B2="q",17,18)) ))),IF(OR(B2="s",B2="t",B2 ="u",B2="v",B2="w"),IF(B2="s",19,IF(B2="t",20,IF(B 2="u",21,IF(B2="v",22,23)) )),IF(B2="x",24,IF(B2="y",25,IF(B2="z",26,"error - type a letter"))))))) z 26 f 6 b 2 d 4 j 10 t 20 58 error - type a letter Formula IF's-26 in one equation "Ron Rosenfeld" escribió en el mensaje ... On Fri, 18 Feb 2005 11:41:01 -0800, "JayE" wrote: I am trying to create a worksheet that will allocate operating expenses to 6 different departments. I have 14 allocation methods on tab "Alloc" that are identified with letters A thru L. On the expense alloction tab I have assigend a letter(A thru L) in column "E" to identify the allocation method. My formula is: =IF($E7="A",($D7*'Alloc'!E$7),IF($E7="B"($D7*'All oc'!E$10).....thru "L". However, my formula will only accept the if statments thur letter H ( 7 nested). Is there any way around this that will allow me to include the other 7 allocation methods in my if statments? As you can probably tell I'm pretty basic. Thanks Take a look at VLOOKUP in HELP. You should probably use VLOOKUP. On your Alloc worksheet, set up a table with A-L in column 1, and your "allocation method" in column 2. Then use a formula of the form: =$D7 * VLOOKUP($E7, 'Alloc'!$A$1:$B$n,2) --ron |
7+ if statements
Antonyo
Nice work. Hard to read though!! Ron Try: = $E7="A" * $D7*'Alloc'!E$7 + $E7="B"*$D7*'Alloc'!E$10 + $E7="C"*$D7*'Alloc'! and so on. Explanation If E7 = A, this is interpretted as 1, that part of your formula becomes 1*$D7*'Alloc'!E$7 When E7 is anything except 'A', E7=A is interpressted as 0. That part of the formula becomes 0*$D7*'Alloc'!E$7 (ie 0) When you group them all together, read the '+' sign as a separation between each part of the formula and it becomes simple to read. HTH Steve "Antonyo" wrote in message ... letter number in the alphabet a =IF(OR(B2="a",B2="b",B2="c",B2="d",B2="e",B2="f"), IF(B2="a",1,IF(B2="b",2,IF (B2="c",3,IF(B2="d",4,IF(B2="e",5,6))))),IF(OR(B2= "g",B2="h",B2="i",B2="j",B 2="k",B2="l"),IF(B2="g",7,IF(B2="h",8,IF(B2="i",9, IF(B2="j",10,IF(B2="k",11, 12))))),IF(OR(B2="m",B2="n",B2="o",B2="p",B2="q",B 2="r"),IF(B2="m",13,IF(B2= "n",14,IF(B2="o",15,IF(B2="p",16,IF(B2="q",17,18)) ))),IF(OR(B2="s",B2="t",B2 ="u",B2="v",B2="w"),IF(B2="s",19,IF(B2="t",20,IF(B 2="u",21,IF(B2="v",22,23)) )),IF(B2="x",24,IF(B2="y",25,IF(B2="z",26,"error - type a letter"))))))) z 26 f 6 b 2 d 4 j 10 t 20 58 error - type a letter Formula IF's-26 in one equation "Ron Rosenfeld" escribió en el mensaje ... On Fri, 18 Feb 2005 11:41:01 -0800, "JayE" wrote: I am trying to create a worksheet that will allocate operating expenses to 6 different departments. I have 14 allocation methods on tab "Alloc" that are identified with letters A thru L. On the expense alloction tab I have assigend a letter(A thru L) in column "E" to identify the allocation method. My formula is: =IF($E7="A",($D7*'Alloc'!E$7),IF($E7="B"($D7*'All oc'!E$10).....thru "L". However, my formula will only accept the if statments thur letter H ( 7 nested). Is there any way around this that will allow me to include the other 7 allocation methods in my if statments? As you can probably tell I'm pretty basic. Thanks Take a look at VLOOKUP in HELP. You should probably use VLOOKUP. On your Alloc worksheet, set up a table with A-L in column 1, and your "allocation method" in column 2. Then use a formula of the form: =$D7 * VLOOKUP($E7, 'Alloc'!$A$1:$B$n,2) --ron |
7+ if statements
Yes, definitely! Use a table and VLOOKUP, PLEASE!
On Sun, 20 Feb 2005 19:59:53 +1100, "Steve" wrote: Antonyo Nice work. Hard to read though!! Ron Try: = $E7="A" * $D7*'Alloc'!E$7 + $E7="B"*$D7*'Alloc'!E$10 + $E7="C"*$D7*'Alloc'! and so on. Explanation If E7 = A, this is interpretted as 1, that part of your formula becomes 1*$D7*'Alloc'!E$7 When E7 is anything except 'A', E7=A is interpressted as 0. That part of the formula becomes 0*$D7*'Alloc'!E$7 (ie 0) When you group them all together, read the '+' sign as a separation between each part of the formula and it becomes simple to read. HTH Steve "Antonyo" wrote in message ... letter number in the alphabet a =IF(OR(B2="a",B2="b",B2="c",B2="d",B2="e",B2="f"), IF(B2="a",1,IF(B2="b",2,IF (B2="c",3,IF(B2="d",4,IF(B2="e",5,6))))),IF(OR(B2= "g",B2="h",B2="i",B2="j",B 2="k",B2="l"),IF(B2="g",7,IF(B2="h",8,IF(B2="i",9, IF(B2="j",10,IF(B2="k",11, 12))))),IF(OR(B2="m",B2="n",B2="o",B2="p",B2="q",B 2="r"),IF(B2="m",13,IF(B2= "n",14,IF(B2="o",15,IF(B2="p",16,IF(B2="q",17,18)) ))),IF(OR(B2="s",B2="t",B2 ="u",B2="v",B2="w"),IF(B2="s",19,IF(B2="t",20,IF(B 2="u",21,IF(B2="v",22,23)) )),IF(B2="x",24,IF(B2="y",25,IF(B2="z",26,"error - type a letter"))))))) z 26 f 6 b 2 d 4 j 10 t 20 58 error - type a letter Formula IF's-26 in one equation "Ron Rosenfeld" escribió en el mensaje ... On Fri, 18 Feb 2005 11:41:01 -0800, "JayE" wrote: I am trying to create a worksheet that will allocate operating expenses to 6 different departments. I have 14 allocation methods on tab "Alloc" that are identified with letters A thru L. On the expense alloction tab I have assigend a letter(A thru L) in column "E" to identify the allocation method. My formula is: =IF($E7="A",($D7*'Alloc'!E$7),IF($E7="B"($D7*'All oc'!E$10).....thru "L". However, my formula will only accept the if statments thur letter H ( 7 nested). Is there any way around this that will allow me to include the other 7 allocation methods in my if statments? As you can probably tell I'm pretty basic. Thanks Take a look at VLOOKUP in HELP. You should probably use VLOOKUP. On your Alloc worksheet, set up a table with A-L in column 1, and your "allocation method" in column 2. Then use a formula of the form: =$D7 * VLOOKUP($E7, 'Alloc'!$A$1:$B$n,2) --ron |
7+ if statements
On Sun, 20 Feb 2005 19:59:53 +1100, "Steve" wrote:
Antonyo Nice work. Hard to read though!! Ron Try: = $E7="A" * $D7*'Alloc'!E$7 + $E7="B"*$D7*'Alloc'!E$10 + $E7="C"*$D7*'Alloc'! and so on. Explanation If E7 = A, this is interpretted as 1, that part of your formula becomes 1*$D7*'Alloc'!E$7 When E7 is anything except 'A', E7=A is interpressted as 0. That part of the formula becomes 0*$D7*'Alloc'!E$7 (ie 0) When you group them all together, read the '+' sign as a separation between each part of the formula and it becomes simple to read. HTH Steve Sure seems a lot more complicated and less flexible than a lookup table. --ron |
7+ if statements
You forgot "harder to maintain".
"Ron Rosenfeld" wrote in message ... On Sun, 20 Feb 2005 19:59:53 +1100, "Steve" wrote: Antonyo Nice work. Hard to read though!! Ron Try: = $E7="A" * $D7*'Alloc'!E$7 + $E7="B"*$D7*'Alloc'!E$10 + $E7="C"*$D7*'Alloc'! and so on. Explanation If E7 = A, this is interpretted as 1, that part of your formula becomes 1*$D7*'Alloc'!E$7 When E7 is anything except 'A', E7=A is interpressted as 0. That part of the formula becomes 0*$D7*'Alloc'!E$7 (ie 0) When you group them all together, read the '+' sign as a separation between each part of the formula and it becomes simple to read. HTH Steve Sure seems a lot more complicated and less flexible than a lookup table. --ron |
| All times are GMT +1. The time now is 09:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com