Solved How To next multiple IF AND statements

December 13, 2016 at 11:25:35
Specs: Windows 7
Hi - how do I combine the following individual IF AND statements into one large one?

=IF(AND(A2*B2<=149, IF(D2 =1, 11.61,17.41),,0)
=IF(AND(C2>=149,C2<=199),IF(D2 = 1, 12.16, 18.39),0)
=IF(AND(C2>=200,C2<=249),IF(D2 = 1, 12.84, 19.22),0)
=IF(AND(C2>=250,C2<=299),IF(D2 = 1, 13.69, 20.30),0)
=IF(AND(C2>=300,C2<=399),IF(D2 = 1, 14.50, 21.82),0)
=IF(AND(C2>=400,C2<=499),IF(D2 = 1, 16.84, 25.27),0)
=IF(AND(C2>=500,C2<=599),IF(D2 = 1, 18.55, 28.43),0)
=IF(AND(C2>=600,C2<=699),IF(D2 = 1, 22.04, 32.98),0)
=IF(AND(C2>=700,C2<=799),IF(D2 = 1, 25.19, 39.53),0)
=IF(AND(C2>=800,C2<=899),IF(D2 = 1, 28.43, 42.59),0)
=IF(AND(C2>=900,C2<=980),IF(D2 = 1, 31.49, 47.14),0)


Thank you for your time and assistance.

Stephanie


See More: How To next multiple IF AND statements

Report •

✔ Best Answer
December 13, 2016 at 15:52:23
OK, so it sounds like you don't even need that first formula. The test is always for the "quantity" in Column C with Column D deciding which of the 2 corresponding dollar values is returned.

If that the case, I would do it this way:

Create a table of your Quantities and Dollar Values and store it someplace in your worksheet/workbook.

Just as an example, I'll place the table in I2:K12 as shown below:

         I         J         K
2         0     $11.61    $17.41 
3       150	$12.16 	  $18.39 
4       200	$12.84 	  $19.22 
5       250	$13.69 	  $20.30 
6       300	$14.50 	  $21.82 
7       400	$16.84 	  $25.27 
8       500	$18.55 	  $28.43 
9       600	$22.04 	  $32.98 
10      700	$25.19 	  $39.53 
11      800	$28.43 	  $42.59 
12      900	$31.49 	  $47.14 


Put this formula in E2 and drag it down:

=IF(D2=1,VLOOKUP(C2,$I$2:$K$12,2,1),VLOOKUP(C2,$I$2:$K$12,3,1))

The formula will search for the Quantity in Cx in I2:I12. When it finds the value that is higher than the value in Cx, it returns the values from the previous row.
This occurs because I use 1 (TRUE) as the range_lookup argument. (If you use 0 (FALSE) then VLOOKUP will look for an exact match, which is not what we want in this case.)

The IF uses the value in Dx to determine which of the 2 VLOOKUPs to evaluate.

There is an advantage to using the VLOOKUP method when you have a large number of IF's to deal with:

If you ever need to change the Quantity ranges or the Dollar Values, you can just update the table and not have to change the formula.

Let me know if you have any questions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
December 13, 2016 at 12:13:43
You can't combine them because your first formula doesn't work.

=IF(AND(A2*B2<=149, IF(D2 =1, 11.61,17.41),,0)

What are you trying to do with this formula? I don't see a need for the AND in that formula. The double commas are also confusing.


I'm also not sure when the C2 IF's come into play.

Please explain what you are trying to do in a little more detail.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 13, 2016 at 12:36:13
Thank you for responding. I've tested the formulas and it appears that it does work just fine.

Column A = Length (Hand Entered)
Column B = Width (Hand Entered)
Column C = A*B
Column D = Thickness (Hand entered can be either a 1 or 2. If 1 then it is one value if it is 2 then it is a 2nd value.)
Coulmn E is where the final results go.

so long and short of it I need to do one equation that will say

if C = x and D = 1, price one or D = 2 price two for all of the variations of column c.


COLUMN C Price 1 Price 2
LESS THAN 149 $11.61 $17.41
150-199 $12.16 $18.39
200-249 $12.84 $19.22
250-299 $13.69 $20.30
300-399 $14.50 $21.82
400-499 $16.84 $25.27
500-599 $18.55 $28.43
600-699 $22.04 $32.98
700-799 $25.19 $39.53
800-899 $28.43 $42.59
900-980 $31.49 $47.14

If you know a better way to do this please advise. Thank you again for your quick response.


Report •

#3
December 13, 2016 at 13:01:12
re: "I've tested the formulas and it appears that it does work just fine."

I'm not arguing, but I don't see how this formula could possibly work:

=IF(AND(A2*B2<=149, IF(D2 =1, 11.61,17.41),,0)

Let's start with the simple fact that you have 3 Open parenthesis and only 2 Close parenthesis.

=IF(AND(A2*B2<=149, IF(D2 =1, 11.61,17.41),,0)
   O1  O2             O3                C3  C2

Excel complains every time I try it and and I can see why.

I'll look over the details you've posted and see what I can suggest.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
December 13, 2016 at 13:17:50
Sorry, I went back over my notes. I apparently edited that line. The first line should be:

=IF(A2*B2<=149, IF(D2 =1, 11.61,17.41),0)

Does that look better?

I copied the wrong thing as I was attempting to figure it out myself. Sorry for not double checking when you first called me on it.

Thanks for the heads up. I really appreciate your time and efforts with this. My brain is fried.

S~

message edited by samckeon


Report •

#5
December 13, 2016 at 15:52:23
✔ Best Answer
OK, so it sounds like you don't even need that first formula. The test is always for the "quantity" in Column C with Column D deciding which of the 2 corresponding dollar values is returned.

If that the case, I would do it this way:

Create a table of your Quantities and Dollar Values and store it someplace in your worksheet/workbook.

Just as an example, I'll place the table in I2:K12 as shown below:

         I         J         K
2         0     $11.61    $17.41 
3       150	$12.16 	  $18.39 
4       200	$12.84 	  $19.22 
5       250	$13.69 	  $20.30 
6       300	$14.50 	  $21.82 
7       400	$16.84 	  $25.27 
8       500	$18.55 	  $28.43 
9       600	$22.04 	  $32.98 
10      700	$25.19 	  $39.53 
11      800	$28.43 	  $42.59 
12      900	$31.49 	  $47.14 


Put this formula in E2 and drag it down:

=IF(D2=1,VLOOKUP(C2,$I$2:$K$12,2,1),VLOOKUP(C2,$I$2:$K$12,3,1))

The formula will search for the Quantity in Cx in I2:I12. When it finds the value that is higher than the value in Cx, it returns the values from the previous row.
This occurs because I use 1 (TRUE) as the range_lookup argument. (If you use 0 (FALSE) then VLOOKUP will look for an exact match, which is not what we want in this case.)

The IF uses the value in Dx to determine which of the 2 VLOOKUPs to evaluate.

There is an advantage to using the VLOOKUP method when you have a large number of IF's to deal with:

If you ever need to change the Quantity ranges or the Dollar Values, you can just update the table and not have to change the formula.

Let me know if you have any questions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
December 14, 2016 at 07:13:26
Thank you so much for your assistance. It worked GREAT! Your the best!

How do I mark this finished?

Stephanie

message edited by samckeon


Report •

#7
December 14, 2016 at 09:41:52

Report •

#8
January 18, 2017 at 09:03:20
So I'm back...hoping that you can help me out with the new addition of two variables.

we are now adding a 3rd width (Column P)

M N O P
1 Q 1" 2" 4"
2 0 $0.00 $0.00 $0.00
3 1 $15.61 $23.52 $44.60
4 150 $16.82 $24.61 $46.84
5 200 $17.28 $26.02 $48.89
6 250 $18.41 $27.50 $52.04
7 300 $19.69 $29.35 $55.92
8 400 $22.76 $33.90 $64.68
9 500 $25.37 $38.09 $72.41
10 600 $29.58 $44.43 $84.57
11 700 $33.90 $50.94 $96.64
12 800 $38.09 $57.24 $108.63
13 900 $42.28 $64.11 $120.52


The current equation I am using is: =IF(K20=1,VLOOKUP(M20,STD Cap SERIES 400!R8:T19,2,1),VLOOKUP(M20,STD Cap SERIES 400!R8:T19,3,1))


The 4th variable is if (G20 + I20) > 24.5 and/or (C20 + E20) > 40 the dollar amount that the vlookup above needs to be multiplied by 1.5

Thanks for your assistance.

Stephanie


Report •

#9
January 18, 2017 at 09:36:50
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.

Second:

re: "The 4th variable is..."

I'm a tad confused. I only see 1 variable: IF K20=1. What are variables 2 and 3, that gets us to a "4th"?

Third:

re: "(G20 + I20) > 24.5 and/or (C20 + E20) > 40 "

Is it AND or OR? There is a big difference as far as Excel is concerned. Do both of those conditions need to be met or is only one needed in order the trigger the multiplication?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
January 18, 2017 at 10:14:44
Thank you for the posting tips; they are very helpful (I was wondering how you posted
the table looking so nicely.)

You had helped me above change from a ton of nesting ifs to using a vlook up box for
just a 1" and 2". The equation you gave me to use is:

 =IF(K20=1,VLOOKUP(M8,STDCAPSERIES400!M2:O13,2,1),
VLOOKUP(M8,STDCAPSERIES400!M2:O13,3,1))  

K20 and M20 are on my PRICE tab; K20 is the Thickness field (1, 2, or 4"), M20 Is the
SQ Foot value that needs to be looked up on the STDCAPSERIES400 tab in M2:M13

Now I need to add a 4" column as listed below: (This chart is on the
STDCAPSERIES400 tab)

	M	N	O	P
1	Length	1"	2"	4"
2	0	$0.00	$0.00	$0.00
3	1	$10.74	$16.03	$30.58
4	150	$11.20	$16.98	$32.17
5	200	$11.89	$17.82	$33.62
6	250	$12.59	$18.89	$35.82
7	300	$13.42	$20.11	$38.44
8	400	$15.57	$23.49	$44.60
9	500	$17.41	$26.19	$49.76
10	600	$20.36	$30.58	$58.01
11	700	$23.27	$34.99	$66.48
12	800	$26.27	$39.44	$74.75
13	900	$29.14	$43.71	$81.36

 

Not sure how I do this with the 3 options (1", 2" and 4").

On top of it, If

(G20 + I20) > 24.5 or (C20 + E20) > 40
the dollar amount selected using the vlookup above needs to be multiplied by 1.5.
EITHER condition has to be met in order for the value to be multiplied by the 1.5.


I am very sorry I was not clearer above. Still getting used to posting to the msg boards. Thank you again for your assistance.


Report •

#11
January 18, 2017 at 10:57:49
So I figured this out:

=IF(K20=1,VLOOKUP(M20,STDCAPSERIES400!M2:P13,2,1),IF(K20=2,VLOOKUP(M20,STDCAPSERIES400!M2:P13,3,1),VLOOKUP(M20,STDCAPSERIES400!M2:P13,4,1)))

That takes into consideration the 4" addition. However I am having an issue trying to figure out how to multiple the results by 1.5 if

(G20 + I20) > 24.5 or (C20 + E20) > 40
the dollar amount selected using the vlookup

Thanks again

message edited by samckeon


Report •

#12
January 18, 2017 at 12:24:57
I am going to offer 3 solutions that I think meet your latest requirements. The first suggestion is a brute force method that simply uses a bunch of IF's based on your requirements. The second suggestion is a bit more elegant and the third is even more elegant.

I'll post the suggestion, then the explanation.

Suggestion #1, Straight Up Brute Force

=IF(OR(SUM(G20,I20)>24.5,SUM(C2,E20)>40),
IF(K20=1,VLOOKUP(M20,STDCAPSERIES400!M2:P13,2,1),
IF(K20=2,VLOOKUP(M20,STDCAPSERIES400!M2:P13,3,1),
VLOOKUP(M20,STDCAPSERIES400!M2:P13,4,1)))*1.5,
IF(K20=1,VLOOKUP(M20,STDCAPSERIES400!M2:P13,2,1),
IF(K20=2,VLOOKUP(M20,STDCAPSERIES400!M2:P13,3,1),
VLOOKUP(M20,STDCAPSERIES400!M2:P13,4,1))))

Explanation, in pieces...

IF(OR(SUM(G20,I20)>24.5,SUM(C2,E20)>40)

If this OR is TRUE (meaning either of the SUM functions meet your criteria) then the formula will evaluate the following portion, which is nothing more than a 3 part Nested IF, similar to the formula that you are using now, which returns a value from a specific column of the VLOOKUP table based in the value in K20. Once that value is returned, it is multiplied by 1.5.

IF(K20=1,VLOOKUP(M20,STDCAPSERIES400!M2:P13,2,1),
IF(K20=2,VLOOKUP(M20,STDCAPSERIES400!M2:P13,3,1),
VLOOKUP(M20,STDCAPSERIES400!M2:P13,4,1)))*1.5

If the OR is FALSE, meaning the neither of your SUM functions meet the criteria, then the second 3 part Nested IF is evaluated, which not multiplied by 1.5.

IF(K20=1,VLOOKUP(M20,STDCAPSERIES400!M2:P13,2,1),
IF(K20=2,VLOOKUP(M20,STDCAPSERIES400!M2:P13,3,1),
VLOOKUP(M20,STDCAPSERIES400!M2:P13,4,1)))

This is a "Brute Force" method because just about every step is dealt with individually.

Suggestion 2, A Bit More Elegant:

=(1+.5*OR(SUM(G20,I20)>24.5,SUM(C2,E20)>40))*
IF(K20=1,VLOOKUP(M20,STDCAPSERIES400!M2:P13,2,1),
IF(K20=2,VLOOKUP(M20,STDCAPSERIES400!M2:P13,3,1),
VLOOKUP(M20,STDCAPSERIES400!M2:P13,4,1)))

We can shorten the formula by using the TRUE or FALSE returned by the OR to determine what multiplier to use on the value returned by the VLOOKUP. You will notice that the 3 part Nested IF is only used once in this formula. This is because of this section:

=(1+.5*OR(SUM(G20,I20)>24.5,SUM(C2,E20)>40))*

When the OR is TRUE, Excel sees that as a 1. Simple math tells us that 1 + .5*1 = 1.5, therefore the value returned by the proper VLOOKUP will be multiplied by 1.5. When the OR is FALSE, Excel sees that as a 0. We know that 1 + .5*0 = 1, so the value returned by the VLOOKUP will be multiplied by 1 and displayed.

Suggestion #3, Even More Elegant:

=(1+.5*(OR(SUM(G20,I20)>24.5,SUM(C2,E20)>40)))*
VLOOKUP(M20,STDCAPSERIES400!M2:P13,
MATCH(K20&CHAR(34),STDCAPSERIES400!M1:P1,0),1)

In this version, you will notice that there are no IF statements. That is because we are going to use the value in K20 to (more or less) directly determine which column to return the VLOOKUP value from.

I have already explained this part, which determines the multiplier. That hasn't changed:

=(1+.5*(OR(SUM(G20,I20)>24.5,SUM(C2,E20)>40)))*

Now, let's look at the 2nd part of the formula:

VLOOKUP(M20,STDCAPSERIES400!M2:P13,
MATCH(K20&CHAR(34),STDCAPSERIES400!M1:P1,0),1)

Let's pull out the MATCH portion:

MATCH(K20&CHAR(34),STDCAPSERIES400!M1:P1,0)

And then further pull out:

K20&CHAR(34)

What is does is take the value in K20 and append a double quote onto it, returning 1" or 2" or 4".

The MATCH function then matches that string with the values in the column headings in STDCAPSERIES400!M1:P1. The MATCH function always returns the column number from within the lookup_array (M=1, N=2, O=3, P=4), so once that number has been determined, the VLOOKUP can use it as the col_index number and return the value from the proper column.

Isn't Excel fun? ;-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#13
January 18, 2017 at 12:49:16
YOU my friend are the bomb digitty! I am so glad I found you! Thank you so much for your assistance! Brute force it is!

And yes...Love Excel but trying to expand my knowledge is a bit painful! ;)

I so appreciate your time!

Stephanie


Report •

#14
January 18, 2017 at 16:10:53
I don't know if you are familiar with the Formula Evaluator feature, but it can be a great resource for working with formulas and seeing what they do step by step. You should try it on the suggestions I offered to help you understand them better.

The main reason for suggesting the 3 methods was not only to help you find a working solution, but also to show you that by combining different functions you can get Excel to do some of the work for you. There are times when you won't be able to use hard-coded numbers in a formula because you won't know what those numbers are until some previous calculation have been performed.

Just trying to get you thinking...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#15
January 19, 2017 at 07:33:52
Thank you; I have copied your examples and explanations to my personal file for Excel. Your insight has been extremely helpful. I'm attempting to do something a bit different that doesn't require a vlookup but based upon the thickness the length x width value would be multiplied by a particular value and if that value doesn't equal x it needs to be x. I will post a new thread if I cannot figure it out based on all what you have shared with me so far.

Thank you for being out there online and being willing to help a stranger.


Report •

Ask Question