Formula result based on Multiple IFs

[Neil M]

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.

 

[Pete_UK]

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

 

[Sam Wilson]

=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?

 

[Sandy Mann]

One way:

With Wall, Slab & Footing in K2:K4 and

Each Way Top, Each Way Bottom, Each Way Vertical, Each Way Horizontal, Top,
Bottom, Each Face, Each Way Each Face

in L1:T1,

Now enter the relevant formulas to calculate the required result in L2:T4 so
that you have 27 formulas in those cells.

Finally use:

=VLOOKUP(E1,K1:T4,MATCH(I1,L1:T1,FALSE)+1,FALSE)

To choose the appropriate return for the formulas


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Neil M]

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

 

[Neil M]

Never Mind! I forgot to drag your cell I1 down to I4 in one location.

Sorry, Everything is good now!

Thanks!

 

[Pete_UK]

That's good to hear - thanks for feeding back, Neil.

Pete