Multiple IF functions with Formulas within one cell.

[Hughes]

Example:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<TABLE style="WIDTH: 426pt; BORDER-COLLAPSE: collapse; mso-padding-alt: 0in 0in 0in 0in" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=568 u1:str><COLGROUP><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" span=2 width=64><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 694" width=19><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" span=2 width=64><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 694" width=19><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" span=3 width=64><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 694" width=19><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" width=64><TBODY><TR style="HEIGHT: 12.95pt; mso-height-source: userset; mso-yfti-irow: 0; mso-yfti-firstrow: yes"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" width=64><o></o>


</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" width=64>A<o></o>


</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 14pt; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" width=19><o></o>


</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" width=64>C<o></o>


</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" width=64>D<o></o>


</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 14pt; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" width=19><o></o>


</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" width=64>F<o></o>


</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" width=64>G<o></o>


</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" width=64>H<o></o>


</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 14pt; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" width=19><o></o>


</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 48pt; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" width=64>J<o></o>

​

</TD></TR><TR style="HEIGHT: 12.95pt; mso-height-source: userset; mso-yfti-irow: 1; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in" u1:num>1<o></o>



</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt">A<o></o>


</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in"><o></o>

​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt" u1:num>72<o></o>

​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt" u1:num>48<o></o>



</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in"><o></o>


</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt">NO<o></o>


</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">NO<o></o>


</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-top-alt: solid windowtext .5pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt">YES<o></o>


</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3; PADDING-TOP: 0in"><o></o>

​

</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; PADDING-RIGHT: 0in; HEIGHT: 12.95pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid; PADDING-TOP: 0in; mso-border-alt: solid windowtext .5pt" u1:num>702<o></o>



</TD></TR></TBODY></TABLE>
<o></o>
I want J to say:<o></o>
<o></o>
IF (A1 = A) & (F1 = NO) & (G1 = NO) & (H1 = NO) then ((C1 + D1)*4.85) or<o></o>
<o></o>
IF (A1 = A) & (F1 = YES) & (G1 = NO) & (H1 = NO) then ((C1 + D1)*5.15) or<o></o>
<o></o>
IF (A1 = A) & (F1 = NO) & (G1 = YES) & (H1 = NO) then ((C1 + D1)*5.55) or<o></o>
<o></o>
IF (A1 = A) & (F1 = NO) & (G1 = NO) & (H1 = YES) then ((C1 + D1)*5.85) or<o></o>
<o></o>
IF (A1 = A) & (F1 = YES) & (G1 = YES) & (H1 = NO) then ((C1 + D1)*5.85) or<o></o>
<o></o>
IF (A1 = A) & (F1 = NO) & (G1 = YES) & (H1 = YES) then ((C1 + D1)*6.55) or<o></o>
<o></o>
IF (A1 = A) & (F1 = YES) & (G1 = YES) & (H1 = YES) then ((C1 + D1)*6.85) or<o></o>
<o></o>
IF (A1 = A) & (F1 = NO) & (G1 = NO) & (H1 = NO) then ((C1 + D1)*5.15) or<o></o>
<o></o>
IF (A1 = A) & (F1 = YES) & (G1 = NO) & (H1 = NO) then ((C1 + D1)*5.45) or<o></o>
<o></o>
IF (A1 = A) & (F1 = NO) & (G1 = YES) & (H1 = NO) then ((C1 + D1)*5.85) or<o></o>
<o></o>
IF (A1 = A) & (F1 = NO) & (G1 = NO) & (H1 = YES) then ((C1 + D1)*6.15) or<o></o>
<o></o>
IF (A1 = A) & (F1 = YES) & (G1 = YES) & (H1 = NO) then ((C1 + D1)*6.15) or<o></o>
<o></o>
IF (A1 = A) & (F1 = NO) & (G1 = YES) & (H1 = YES) then ((C1 + D1)*6.85) or<o></o>
<o></o>
IF (A1 = A) & (F1 = YES) & (G1 = YES) & (H1 = YES) then ((C1 + D1)*7.15) or.............................<o></o>
<o></o>
And so on through A1 = N. I would also want to extend the options in F-H to F-P. Help is much appreciated.<o></o>
<o></o>
I've wanted to do something like this on Access as I've seen a coworker with something similar. Unfortunately I've never used Access and need to get this going sooner than I could learn. <o></o>

 

[Hughes]

Could I have the value for Column A hidden off the worksheet. For eample, I would have a drop down In A1 that would pull from a list of Series A, Series B, Series C, Series D, etc that's hidden. Then I could have a formula hidden in another cell that says IF A1 = Series A then the base for J is 4.85. IF A1 = Series B then the base for J is 5.15.

 

[p45cal]

Could I have the value for Column A hidden off the worksheet. For eample, I would have a drop down In A1 that would pull from a list of Series A, Series B, Series C, Series D, etc that's hidden. Then I could have a formula hidden in another cell that says IF A1 = Series A then the base for J is 4.85. IF A1 = Series B then the base for J is 5.15.

I don't think you need to do that, msg#11 incorporates it all in one cell.

 

[Hughes]

I'm not getting an error message but the answer is "0" regardless of what I have in A1. I added the values for I1 & J1 but still "0".

 

[p45cal]

Working formulae:
Copy down the formulae in L1,M1 and N1.
The three formulae all give the same result, but those in columns M and N will be shorter once you extend it to handle more possibilities in column A. These shorter formulae depend on the first letter of the cell in column A being the series name (upper or lower case, it doesn't matter).
Just choose whichever of the three you're comfortable with.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	A		72	48		NO	NO	NO	NO	NO		582	582	582
2	A		72	48		NO	NO	YES	NO	NO		702	702	702
3	A		72	48		NO	YES	NO	NO	NO		666	666	666
4	A		72	48		NO	YES	YES	NO	NO		786	786	786
5	A		72	48		YES	NO	NO	NO	NO		618	618	618
6	A		72	48		YES	YES	NO	NO	NO		702	702	702
7	A		72	48		YES	YES	YES	NO	NO		822	822	822
8	B		72	48		NO	NO	NO	NO	NO		618	618	618
9	B		72	48		NO	NO	YES	NO	NO		738	738	738
10	B		72	48		NO	YES	NO	NO	NO		702	702	702
11	B		72	48		NO	YES	YES	NO	NO		822	822	822
12	B		72	48		YES	NO	NO	NO	NO		654	654	654
13	B		72	48		YES	YES	NO	NO	NO		738	738	738
14	B		72	48		YES	YES	YES	NO	NO		858	858	858
Sheet

 

[Hughes]

PERFECT. Thank you!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

You saved me countless hours! I wan't even close to heading in the right direction!

 

[p45cal]

You might be interested in another if the YESses and NOs in column F:J (or F:N whatever) are always next to each other (no gaps between columns):

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	W	Y
1	A		72	48		NO	NO	NO	NO	NO		582
2	a		72	48		NO	NO	YES	NO	NO		702
3	about		72	48		NO	YES	NO	YES	NO		720
4	A		72	48		NO	YES	YES	NO	NO		786
5	A		72	48		YES	NO	NO	NO	NO		618
6	A		72	48		YES	YES	NO	YES	NO		756
7	A		72	48		YES	YES	YES	NO	NO		822
8	B		72	48		NO	NO	NO	NO	YES		696
9	B		72	48		NO	NO	YES	NO	NO		738
10	bills		72	48		NO	YES	NO	YES	NO		756
11	B		72	48		NO	YES	YES	NO	NO		822
12	b		72	48		YES	NO	NO	YES	YES		786
13	B		72	48		YES	YES	NO	NO	NO		738
14	B		72	48		YES	YES	YES	NO	YES		936
Sheet

where the
{0.3,0.7,1,0.45,0.65}
corresponds to columns F:J

and the
4.85,5.15,6.3
corresponds to "A","B" or "C" etc. in column A


And if you were prepared to put the values in row 1 (then hide it) you could simplify further:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	W	Z
1						0.3	0.7	1	0.45	0.65
2	A		72	48		NO	NO	NO	NO	NO		582
3	a		72	48		NO	NO	YES	NO	NO		702
4	about		72	48		NO	YES	NO	YES	NO		720
5	A		72	48		NO	YES	YES	NO	NO		786
6	A		72	48		YES	NO	NO	NO	NO		618
7	A		72	48		YES	YES	NO	YES	NO		756
8	A		72	48		YES	YES	YES	NO	NO		822
9	B		72	48		NO	NO	NO	NO	YES		696
10	B		72	48		NO	NO	YES	NO	NO		738
11	bills		72	48		NO	YES	NO	YES	NO		756
12	B		72	48		NO	YES	YES	NO	NO		822
13	b		72	48		YES	NO	NO	YES	YES		786
14	B		72	48		YES	YES	NO	NO	NO		738
15	B		72	48		YES	YES	YES	NO	YES		936
Sheet

And still further shorten it if you were to have a lookup table for the Series (A,B, C, D etc,) as MrKowz suggested in msg#2.

 

[Hughes]

Even Better. One question so I can learn. What is the "-64" in the formula? Where does that particular number come from? And one question for aesthetics. Is there any way to make what is Y and Z in your column appear simply blank or "0" until information is entered. This way when the blacnk workbook is opened there isn't a "#VALUE!" error?

 

[p45cal]

Even Better. One question so I can learn. What is the "-64" in the formula? Where does that particular number come from? And one question for aesthetics. Is there any way to make what is Y and Z in your column appear simply blank or "0" until information is entered. This way when the blacnk workbook is opened there isn't a "#VALUE!" error?

CODE("A") is 65
CODE("B") is 66
CODE("C") is 67
etc.
subtract 64 from each and you get 1,2,3 etc. which is used in the CHOOSE function.

Hiding errors:
pre xl2007:
=if(iserror(the_formula),"",the_formula)

xl2007 onwards:
iferror(the_formula,"")

where the_formula is whichever of the formulae you've opted for.

 

[PaddyD]

I was just passing by....

re hiding errors - it is important to note that error masking like (using iferror() etc)that hides all errors, including those generated as a result of dodgy formula syntax. this can be a bad thing, especially when you've got complex formulas to work with.

e.g.

=if(iserror(sm(a1:a10),"No sales, so someone's getting fired",sum(a1:a10))

...is not an error you'd want to mask.

 

[p45cal]

While replying to your question on the origins of -64 in the equation, I noticed I made a mistake in the formula in M1 of msg#14, it should have included the INDEX function, so I've shown it below in M1, corrected.

While I'm at it, take a look at the short formula in L2, this is to show you how to use a lookup table, which will mean however many columns F:? you want to use and however many series A,B,C etc. you want to use, the formula will get no longer than the one in L2 below. It has the additional benefit, that a changing any of the values in the lookup table, or any of the values in (the hidden) row 1, will immediately be reflected in all the calculations, so you won't have to set about editing lots of formulae to correct a mistake.

The green area is the look up table which I named SeriesTable. This doesn't have to be on the same sheet, and can be anywhere in the workbook.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1						0.3	0.7	1	0.45	0.65
2	A		72	48		NO	NO	NO	NO	NO		582	582
3	a		72	48		NO	NO	YES	NO	NO		702	702
4	a		72	48		NO	YES	NO	YES	NO		720	720
5	a		72	48		NO	YES	YES	NO	NO		786	786
6	A		72	48		YES	NO	NO	NO	NO		618	618
7	A		72	48		YES	YES	NO	YES	NO		756	756
8	A		72	48		YES	YES	YES	NO	NO		822	822
9	B		72	48		NO	NO	NO	NO	YES		696	696
10	B		72	48		NO	NO	YES	NO	NO		738	738
11	b		72	48		NO	YES	NO	YES	NO		756	756
12	B		72	48		NO	YES	YES	NO	NO		822	822
13	b		72	48		YES	NO	NO	YES	YES		786	786
14	B		72	48		YES	YES	NO	NO	NO		738	738
15	B		72	48		YES	YES	YES	NO	YES		936	936
16
17
18			A	4.85
19			B	5.15
20			C	6.3
21			D	6.75
22			E	7
23			F	7.1
24			G	7.2
25			H	7.3
26			I	7.4
27			J	7.5
28			K	7.6
29			L	7.7
30			M	7.8
31			N	7.9
32
Sheet