Multiple "IF" formula

[racingsnake2000]

Hello All,
First Post in the Forum....
As most of you are I'm sure, I'm a self taught Excel user.
I have looked through quite a few threads trying to find a similar formula but to no avail.
What I'm trying to do is apply multiple rules to a particular cell to allow the input value to apply to the value of another cell multiplied by a factor. The factors are determined by the cell with the text in and the contents thereof.
I nearly failed maths at school so yeah, it takes me a little while!

My problem is this: The formula works for the first three options(up to "DUPLEX S. Steel", but then after that it says I have too many arguments for this function.
I usually solve these by ages of trial and error but its not working this time....
Maybe you could spot something that looks out of place or better still, simplify it?
Thanks for your help!

=IF($S4="CARBON STEEL",(AT$3),IF($S4="LOW-ALY STL",(AT$3*$AT$1)),IF($S4="DUPLEX S. STEEL",(AT$3*$AX$1),IF($S4="STNLS STEEL",(AT$3*$AV$1))))

 

[racingsnake2000]

I just solved it....
It was only when I had a look at it once I had posted it that I saw the double brackets in the middle of the formula that I realised that I had isolated the two sides of the equation...
I deleted one pair of brakets and it works now....
Is this the only way to enter this sort of formula or are there shorter more simple methods?
The working formula is as follows:
=IF($S4="CARBON STEEL",(AT$3),IF($S4="LOW-ALY STL",(AT$3*$AT$1),IF($S4="DUPLEX S. STEEL",(AT$3*$AX$1),IF($S4="STNLS STEEL",(AT$3*$AV$1)))))

 

[T. Valko]

I just solved it....
It was only when I had a look at it once I had posted it that I saw the double brackets in the middle of the formula that I realised that I had isolated the two sides of the equation...
I deleted one pair of brakets and it works now....
Is this the only way to enter this sort of formula or are there shorter more simple methods?
The working formula is as follows:
=IF($S4="CARBON STEEL",(AT$3),IF($S4="LOW-ALY STL",(AT$3*$AT$1),IF($S4="DUPLEX S. STEEL",(AT$3*$AX$1),IF($S4="STNLS STEEL",(AT$3*$AV$1)))))

If you only have a "few" variables then nesting IFs is just fine.

One thing you could do is remove the extra ( ):

=IF($S4="CARBON STEEL",AT$3,IF($S4="LOW-ALY STL",AT$3*$AT$1,IF($S4="DUPLEX S. STEEL",AT$3*$AX$1,IF($S4="STNLS STEEL",AT$3*$AV$1,""))))

Another way to do it is to make a 2 column table like this:

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 123px"><COL style="WIDTH: 101px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">CARBON STEEL</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">=AT$3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">LOW-ALY STL</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">=AT$3*$AT$1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">DUPLEX S. STEEL</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">=AT$3*$AX$1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">STNLS STEEL</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">=AT$3*$AV$1</TD></TR></TBODY></TABLE>


The left column is the steel type and the right column is the formula.

Then you can use a lookup formula on this table. Like this:

=VLOOKUP($S4,$A$2:$B$5,2,0)

 

[racingsnake2000]

Thanks!
That makes a lot of sense and certainly simplifies things.

 

[T. Valko]

Thanks!
That makes a lot of sense and certainly simplifies things.

You're welcome. Thanks for the feedback!