Multiple IF functions with Formulas within one cell.

H

Hughes

New Member
Joined
Jan 11, 2011
Messages
8
Example:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
<o:p></o:p>
<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:p></o:p>


</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:p></o:p>


</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:p></o:p>


</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:p></o:p>


</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:p></o:p>


</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:p></o:p>


</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:p></o:p>


</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:p></o:p>


</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:p></o:p>


</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:p></o:p>


</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:p></o:p>

​
</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:p></o:p>



</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:p></o:p>


</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:p></o:p>

​
</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:p></o:p>


​
</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:p></o:p>



</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:p></o:p>


</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:p></o:p>


</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:p></o:p>


</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:p></o:p>


</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:p></o:p>

​
</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:p></o:p>



</TD></TR></TBODY></TABLE>
<o:p></o:p>
I want J to say:<o:p></o:p>
<o:p></o:p>
IF (A1 = A) & (F1 = NO) & (G1 = NO) & (H1 = NO) then ((C1 + D1)*4.85) or<o:p></o:p>
<o:p></o:p>
IF (A1 = A) & (F1 = YES) & (G1 = NO) & (H1 = NO) then ((C1 + D1)*5.15) or<o:p></o:p>
<o:p></o:p>
IF (A1 = A) & (F1 = NO) & (G1 = YES) & (H1 = NO) then ((C1 + D1)*5.55) or<o:p></o:p>
<o:p></o:p>
IF (A1 = A) & (F1 = NO) & (G1 = NO) & (H1 = YES) then ((C1 + D1)*5.85) or<o:p></o:p>
<o:p></o:p>
IF (A1 = A) & (F1 = YES) & (G1 = YES) & (H1 = NO) then ((C1 + D1)*5.85) or<o:p></o:p>
<o:p></o:p>
IF (A1 = A) & (F1 = NO) & (G1 = YES) & (H1 = YES) then ((C1 + D1)*6.55) or<o:p></o:p>
<o:p></o:p>
IF (A1 = A) & (F1 = YES) & (G1 = YES) & (H1 = YES) then ((C1 + D1)*6.85) or<o:p></o:p>
<o:p></o:p>
IF (A1 = A) & (F1 = NO) & (G1 = NO) & (H1 = NO) then ((C1 + D1)*5.15) or<o:p></o:p>
<o:p></o:p>
IF (A1 = A) & (F1 = YES) & (G1 = NO) & (H1 = NO) then ((C1 + D1)*5.45) or<o:p></o:p>
<o:p></o:p>
IF (A1 = A) & (F1 = NO) & (G1 = YES) & (H1 = NO) then ((C1 + D1)*5.85) or<o:p></o:p>
<o:p></o:p>
IF (A1 = A) & (F1 = NO) & (G1 = NO) & (H1 = YES) then ((C1 + D1)*6.15) or<o:p></o:p>
<o:p></o:p>
IF (A1 = A) & (F1 = YES) & (G1 = YES) & (H1 = NO) then ((C1 + D1)*6.15) or<o:p></o:p>
<o:p></o:p>
IF (A1 = A) & (F1 = NO) & (G1 = YES) & (H1 = YES) then ((C1 + D1)*6.85) or<o:p></o:p>
<o:p></o:p>
IF (A1 = A) & (F1 = YES) & (G1 = YES) & (H1 = YES) then ((C1 + D1)*7.15) or.............................<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
It is going to be a massive formula - we need to utilize a choose and match in combination with eachother. The match is used to get an index number of 1, 2, 3, 4, etc... and we use it in conjunction with choose.

For example, to just get the first three conditions:

=CHOOSE(MATCH(A1&F1&G1&H1,{"ANoNoNo","AYesNoNo","ANoYesNo"},0),(C1+D1)*4.85,(C1+D1)*5.15,(C1+D1)*5.55)

You're likely going to run into the 255 character limit for a single formula. Consider programming this in VBA or allowing yoruself to have a lookup table.
 
Upvote 0
I see that:
a YES in column F adds 0.3 to the multiplying factor,
a YES in column G adds 0.7 to the multiplying factor,
a YES in column H adds 1.0 to the multiplying factor.

However, you have duplicate statements in your example for all the combinations but with different multiplying factors, so I'm guessing the values in column A might be B in half of them. There's always a difference of 0.3 between the pairs of duplicate statements.

You say "And so on through A1 = N", so values in column A can A,B,C,D,E etc.?
If so is there always an increment of 0.3 between each of the values?

So part of the solution might be along the lines of (assuming "YES" in columns F,G & H is what is significant, anything else assumed to be "NO"):
=(C1+D1)*(4.85+(A1="B")*0.3+(F1="YES")*0.3+(G1="YES")*0.7+(H1="YES")*1)

…too many unknowns to give you a definitive solution at the moment.

 
Upvote 0
It should be F=0.3, G=0.7, h=1.0. So depending on the combination it could be 0.3, 0.7, 1.0, 1.3, 1.7, and 2.0. This will be a worksheet for others to use and the formulas will be hidden. I want hem to simply be able to select YES and NO answers from a dropdown list and have the calculation available for them. Once figured out I would add multiple others, like I=0.45, J=0.65, and so on. It will allow the users to answer questions and cut down on mathematic errors and unknowns.
 
Upvote 0
Then
4.85+(F1="YES")*0.3+(G1="YES")*0.7+(H1="YES")*1+(I1="YES")*0.45+(J1="YES")*0.65
will give you your factor, but this doesn't take into account what might be in column A, which you still haven't indicated.
 
Upvote 0
Column A is the base. I just noticed a mistake in my example. The first 7 scenarios should be (A1 = A). The second Series of 7 should have said (A1 = B). A, B, C, etc. would be the name of different series that could be selected. So I'll say A is Series A. Series A would have a base value of 4.85. If (A1=B), Series B would have a base value of 5.15. If (A1 = C), Series C could have a base vaulue of 6.30, etc.
 
Upvote 0
To expand on what p45cal has suggested:

=LOOKUP(A1,{"A","B","C"},{4.85,5.15,6.30})+(F1="YES")*0.3+(G1="YES")*0.7+(H1="YES")*1+(I1="YES")*0.45+(J1="YES")*0.65
 
Upvote 0
I should have included this in my first answer:
Excel Workbook
ABCDEFGHIJ
1A7248NONONO582
2A7248NONOYES702
3A7248NOYESNO666
4A7248NOYESYES786
5A7248YESNONO618
6A7248YESYESNO702
7A7248YESYESYES822
8B7248NONONO618
9B7248NONOYES738
10B7248NOYESNO702
11B7248NOYESYES822
12B7248YESNONO654
13B7248YESYESNO738
14B7248YESYESYES858
...
Excel 2010
Cell Formulas
RangeFormula
J1=(C1+D1)*(4.85+(A1="B")*0.3+(F1="YES")*0.3+(G1="YES")*0.7+(H1="YES")*1)

Just copy J1's formula down.

Moving on, MrKowz has suggested a good solution which starts:
=LOOKUP(A1,{"A","B","C"},{4.85,5.15,6.30})
but you say there are going to be values all the way to "N"; is there a pattern in the series? -it could severely shorten the final formula.
 
Upvote 0
Unfortunately no pattern for A1. It will just be a bunch of different series that have different random values.
 
Upvote 0
So that's you sorted with a formula in row 1 somewhere like:
Code: 
=(C1+D1)*(LOOKUP(A1,{"A","B","C"},{4.85,5.15,6.3})+(F1="YES")*0.3+(G1="YES")*0.7+(H1="YES")*1+(I1="YES")*0.45+(J1="YES")*0.65)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
868
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
3K
Attrazion
A
E
  • Question Question
VBA code clean up
Replies
9
Views
690
mumps
mumps
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
Adding a custom message.
Replies
1
Views
705
Micron
M

Forum statistics

Threads
1,226,693
Messages
6,192,460
Members
453,725
Latest member
cvsdatreas

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top