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:eek:ffice:eek:ffice" /><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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
825
gvsumich
G
E
  • Question
Adding a custom message.
Replies
1
Views
429
Micron
M
E
  • Question
VBA for Web Scraping help
Replies
0
Views
592
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C

Forum statistics

Threads
1,214,909
Messages
6,122,189
Members
449,072
Latest member
DW Draft

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