Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula result based on Multiple IFs
I have a spreadhseet where I need to have a formula based on any one of
several possible criterium. THis is for calculating the amount of reinforcing steel in a wall slab or footing by linear feet and then over to tons. Cell E1 is a named list (type1) that gives 3 possibles Wall, Slab or Footing Cell I1 lets me choose the configuration (each way top, each way bottom, each way vertical, each way horizontal, top, bottom, each face, each way each face) What I want is as follows: IF I1 = a then formula 1 IF I1 =b then formula 2 IF I1 = c then formula 3 etc etc. However I need all the poosible formulas to be entered I have been trying to use a combination of IF as well as AND, but I cannot get it to work. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula result based on Multiple IFs
If you are using Excel 2003 or earlier then you will be limited to 7
nested functions in a formula, so you might have come up against this limit. One way around this is to set up a table of your variables and the outcome and to use VLOOKUP, but it depends what your formula1, formula2 etc look like as to whether you can use this approach. You can string a number of IFs together like this: =IF(I1="a",formula1,"")&IF(I1="b",formula2,"")&IF( I1="c",formula3,"") and so on. This does not suffer from the limit as the functions are not nested, but again it will not work in all cases. If your formulae return numbers then it will have to be written as: =IF(I1="a",formula1,0)+IF(I1="b",formula2,0)+IF(I1 ="c",formula3,0) so that you are adding zero to the formula in those cases where the criterion is not met. However, if you have a lot of conditions the formula will be very long and difficult to maintain. One other approach is to use a UDF to evaluate a string as if it were a formula, and to build up that string depending on your criteria. Perhaps if you could post a few more details of exactly what you want to do ... ? Hope this helps. Pete On Jun 23, 4:41*pm, Neil M wrote: I have a spreadhseet where I need to have a formula based on any one of several possible criterium. THis is for calculating the amount of reinforcing steel in a wall slab or footing by linear feet and then over to tons. Cell E1 is a named list (type1) that gives 3 possibles Wall, Slab or Footing Cell I1 lets me choose the configuration (each way top, each way bottom, each way vertical, each way horizontal, top, bottom, each face, each way each face) What I want is as follows: IF I1 = a then formula 1 IF I1 =b then formula 2 IF I1 = c then formula 3 etc etc. However I need all the poosible formulas to be entered I have been trying to use a combination of IF as well as AND, but I cannot get it to work. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula result based on Multiple IFs
=IF(I1=A,[formula1],IF(I2=B,[formula2],IF(I3=C,[formula3],[formula4])))
Instead of [formula4] you could have another if statement, but you're limited to 6 or 7 (I forget which). YOu could also have hidden columns, one for each formula, and have an individual IF in each, then add up these columns to get a final result? "Neil M" wrote: I have a spreadhseet where I need to have a formula based on any one of several possible criterium. THis is for calculating the amount of reinforcing steel in a wall slab or footing by linear feet and then over to tons. Cell E1 is a named list (type1) that gives 3 possibles Wall, Slab or Footing Cell I1 lets me choose the configuration (each way top, each way bottom, each way vertical, each way horizontal, top, bottom, each face, each way each face) What I want is as follows: IF I1 = a then formula 1 IF I1 =b then formula 2 IF I1 = c then formula 3 etc etc. However I need all the poosible formulas to be entered I have been trying to use a combination of IF as well as AND, but I cannot get it to work. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula result based on Multiple IFs
Ok, THANKS, PETE!!
I am making progress now, but there appears to be just one glitch. Here is my formula: =IF(I4="Vert",(((F4*12)/H4)),0)+IF(I4="EF Vert",(((F4*12*2)/H4)),0)+IF(I1="EW EF",(((F4*12*2)/H4)),0) Now if I choose Vert it gives me the right number(40), If I choose EF Vert it also gives the right number(80). However, when I choose EW EF it gives me zero when it should give me the same as EF Vert. Any suggestions? ty Neil M "Pete_UK" wrote: If you are using Excel 2003 or earlier then you will be limited to 7 nested functions in a formula, so you might have come up against this limit. One way around this is to set up a table of your variables and the outcome and to use VLOOKUP, but it depends what your formula1, formula2 etc look like as to whether you can use this approach. You can string a number of IFs together like this: =IF(I1="a",formula1,"")&IF(I1="b",formula2,"")&IF( I1="c",formula3,"") and so on. This does not suffer from the limit as the functions are not nested, but again it will not work in all cases. If your formulae return numbers then it will have to be written as: =IF(I1="a",formula1,0)+IF(I1="b",formula2,0)+IF(I1 ="c",formula3,0) so that you are adding zero to the formula in those cases where the criterion is not met. However, if you have a lot of conditions the formula will be very long and difficult to maintain. One other approach is to use a UDF to evaluate a string as if it were a formula, and to build up that string depending on your criteria. Perhaps if you could post a few more details of exactly what you want to do ... ? Hope this helps. Pete On Jun 23, 4:41 pm, Neil M wrote: I have a spreadhseet where I need to have a formula based on any one of several possible criterium. THis is for calculating the amount of reinforcing steel in a wall slab or footing by linear feet and then over to tons. Cell E1 is a named list (type1) that gives 3 possibles Wall, Slab or Footing Cell I1 lets me choose the configuration (each way top, each way bottom, each way vertical, each way horizontal, top, bottom, each face, each way each face) What I want is as follows: IF I1 = a then formula 1 IF I1 =b then formula 2 IF I1 = c then formula 3 etc etc. However I need all the poosible formulas to be entered I have been trying to use a combination of IF as well as AND, but I cannot get it to work. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula result based on Multiple IFs
Never Mind! I forgot to drag your cell I1 down to I4 in one location. Sorry, Everything is good now! Thanks! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula result based on Multiple IFs
That's good to hear - thanks for feeding back, Neil.
Pete On Jun 23, 6:30*pm, Neil M wrote: Never Mind! I forgot to drag your cell I1 down to I4 in one location. Sorry, Everything is good now! Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP based on a formula result | Excel Worksheet Functions | |||
Conditional Result based on Multiple Columns | Excel Worksheet Functions | |||
lock cells based on formula result | Excel Discussion (Misc queries) | |||
how can I have a formula result based on multiple criteria/columns | New Users to Excel | |||
Automatically resize columns based on new formula result | Excel Discussion (Misc queries) |