Multiple If Formulas

[Laurinda]

Hi,
I would like to combine all of these formulas into 1 cell, but can't figure out why it's not working...

=IF(K17="EA",1,FALSE)
=IF(K18="C",100,FALSE)
=IF(J16,SUM(I16*J16/K16),"")

I've tried the following:

=IF(K17="EA",1,FALSE,IF(K18="C",100,FALSE)IF(J16,SUM(I16*J16/K16),""))

But that doesn't work... Here is a paste of the spreadsheet I'm trying to make this formula work on... I would like the sum formula to work in the extended price whether someone writes 1 or EA (which would equal 1) or C (which would equal 100), etc. Also, I do not want to do a seperate spreadsheet with a vlookup formula.

<TABLE style="WIDTH: 205pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=272><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><TBODY><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d9d9d9; WIDTH: 46pt; HEIGHT: 30pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl68 height=40 width=61>Quantity</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; WIDTH: 56pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl68 width=75>Unit Price</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; WIDTH: 41pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl68 width=54>U of M</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; WIDTH: 62pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl68 width=82>Extended Price</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 46pt; HEIGHT: 20.1pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=26 width=61>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>$2.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>$30.00</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 46pt; HEIGHT: 20.1pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=26 width=61>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>$2.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>EA</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>$1.00</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 46pt; HEIGHT: 20.1pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=26 width=61>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>$2.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>$100.00</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 46pt; HEIGHT: 20.1pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=26 width=61>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>$2.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72>$0.30</TD></TR></TBODY></TABLE>

 

[Sharpefiction]

Hi,
I would like to combine all of these formulas into 1 cell, but can't figure out why it's not working...

=IF(K17="EA",1,FALSE)
=IF(K18="C",100,FALSE)
=IF(J16,SUM(I16*J16/K16),"")

I've tried the following:

=IF(K17="EA",1,FALSE,IF(K18="C",100,FALSE)IF(J16,SUM(I16*J16/K16),""))

But that doesn't work... Here is a paste of the spreadsheet I'm trying to make this formula work on... I would like the sum formula to work in the extended price whether someone writes 1 or EA (which would equal 1) or C (which would equal 100), etc. Also, I do not want to do a seperate spreadsheet with a vlookup formula.

I think this is what you're after:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
=IF(K16="EA",1,IF(K16="C",100,IF(ISNUMBER(K16),SUM(I16*J16/K16),"")))<o></o>
<o></o>
My understanding is:<o></o>
Column I relates to your ‘quantity’ column<o></o>
Column J relates to your ‘Unit Price’ column<o></o>
Column K relates to your ‘U of M’ column<o></o>
<o> </o>
Therefore you only want to pull the values from that row. Your original formula was almost there, but you had too many arguments and you were referencing different rows.<o></o>

 

[CWatts]

The reason you are having trouble is because excel sees everyting as an extra.

For example excel sees an IF statement like this:

IF(logical_test, do_this_if_test_is_true, do_this_if_test_is_false)

You're putting stuff after the false section and confusing it.

Try this:
=IF(K17="EA",1,IF(K18="C",100,IF(J16,SUM(I16*J16/K16),"")))

It basically tests K17 and if it's true then it puts 1, but if nit's not true, it does the next IF.

 

[Laurinda]

I tried doing both those formulas, but it's not returning the answer that I would like. I pasted the spreadsheet with the new formula in it. The C equals 100 & the answer should be $0.30 (as shown when I actually type in 100)..

<TABLE style="WIDTH: 205pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=272><COLGROUP><COL style="WIDTH: 46pt; mso-width-source: userset; mso-width-alt: 2230" width=61><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><TBODY><TR style="HEIGHT: 30pt; mso-height-source: userset" height=40><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d9d9d9; WIDTH: 46pt; HEIGHT: 30pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl70 height=40 width=61>Quantity</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; WIDTH: 56pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl70 width=75>Unit Price</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; WIDTH: 41pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl70 width=54>U of M</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; WIDTH: 62pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl70 width=82>Extended Price</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 46pt; HEIGHT: 20.1pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=26 width=61>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>$2.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; WIDTH: 62pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=82>$100.00</TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 46pt; HEIGHT: 20.1pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=26 width=61>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>$2.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>100</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; WIDTH: 62pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 width=82>$0.30</TD></TR></TBODY></TABLE>

These are the exact formulas that I'm using & both of them are giving the answer 100... Thanks for all your help!

=IF(K16="EA",1,IF(K16="C",100,IF(ISNUMBER(K16),SUM(I16*J16/K16),"")))
and
=IF(K20="EA",1,IF(K20="C",100,IF(J20,SUM(I20*J20/K20),"")))

 

[Scott Huish]

It's doing that because that's what you told it to do. Try this:

=(I16*J16)/MAX(1,IF(ISNUMBER(K16),K16,IF(K16="C",100,1)))