Nested If Statements

[Tennisguuy]

I am having a problem with a nested if statement. I have a situation where if the first three digits in cell E38 of a value equal a certain number and or if the value in cell F38 or G38 are less than a value I wanted a statement to appear. This is the formula I used in cell H38

IF(E38="","",IF(AND(F38="",G38=""),"",IF(OR(LEFT(E38,3)=211,LEFT(E38,3)=212,LEFT(E38,3)=213,LEFT(E38,3)=214,LEFT(E38,3)=215,LEFT(E38,3)=216,LEFT(E38,3)=231,LEFT(E38,3)=232,LEFT(E38,3)=233,LEFT(E38,3)=234,LEFT(E38,3)=235,LEFT(E38,3)=236,AND(OR(F38<1000,G38<1000))),"MINIMUM DED IS 1,000/1,000",IF(OR(LEFT(E38,3)=311,LEFT(E38,3)=312,LEFT(E38,3)=313,LEFT(E38,3)=314,LEFT(E38,3)=315,LEFT(E38,3)=316,LEFT(E38,3)=331,LEFT(E38,3)=332,LEFT(E38,3)=333,LEFT(E38,3)=334,LEFT(E38,3)=335,LEFT(E38,3)=336,AND(OR(F38<2000,G38<2000))),"MINIMUM DED IS 2,000/2,000",IF(OR(LEFT(E38,3)=401,LEFT(E38,3)=402,LEFT(E38,3)=403,LEFT(E38,3)=404,LEFT(E38,3)=405,LEFT(E38,3)=406,AND(OR(F38<3000,G38<3000))),"MINIMUM DED IS 3,000/3,000",IF(OR(LEFT(E38,3)=501,LEFT(E38,3)=502,LEFT(E38,3)=503,LEFT(E38,3)=504,LEFT(E38,3)=505,LEFT(E38,3)=506,AND(OR(F38<3000,G38<3000))),"MINIMUM DED IS 3,000/3,000",IF(AND(F38<500,G38<500),"MINIMUM DED IS 500/500","")))))))

For example if the value in cell E38 is 21499 and the value in cell F38 is 500 and the value in cell G38 is 1,000 I wanted the statement minimum ded is 1000/1000 since one of the values in cell F38 or G38 is less than 1000. When I enter those value I get the correct statement. However, if I just changed the value in cell E38 to 31499 and don't change the values in cell F38 or G38 the same statement minimum deductible is 1000/1000 and the statement that should appear is minimum deductible is 2000/2000. However if I change the value in cell F38 from 500 to 1000 the correct statement appears. If I change the value in cell E38 to 7389 and change the value in cell F38 to 250 and value in cell G38 to 500. The statement minimum ded is 1,000/1,000 appears and the correct statement should be minimum deductible is 500/500.

I tried different variations to get the formula to work and in some situation it would but in others it wouldn't.

Is my setup incorrect.

 

[JayBurn]

That's a lot going on...

Give us an example of your table, and what your expected results would be.

If there are only a few variations on the specific numbers you'd probably do better using an array to combine them and use a lookup against a similar array with the desired text.

 

[Tennisguuy]

Here's an example of the table. The code is in column E, Comp is in column F and Coll is in column G


Code Comp Coll Correct statement to appear
21499 500 500 Minimum deductible is 1000/1000
7398 250 500 Minimum deductible is 500/500
01499 500 250 Minimum deductible is 500/500
30499 2000 2000 Leave blank because state is true
30599 1000 1000 Minimum deductible is 2000/2000
40199 2000 2000 Minimum deductible is 3,000/3000

Yes there are only minimum various by each code. There are about 12 different variations for codes where the first digit are in the 2 or 3 series and about six with codes where the first digit is a 4 or 5.

I had to use OR statement in the formula because of the different variations. I didn't know how else to do it. I was going to try an AND statement since for example in the codes beginning with 2 the first three digit will always be between 211 and 236.

 

[AlanY]

a different approach, with helpers in Cols I to L that can be hidden

Excel 2013/2016
	E	F	G	H	I	J	K	L
38	Code	Comp	Coll	Correct statement to appear		1000	2000	3000
39	21499	500	500	Minimum deductible is 1000/1000	1000	211	311	401
40	7389	250	500	Minimum deductible is 500/500	500	212	312	402
41	01499	500	250	Minimum deductible is 500/500	500	213	313	403
42	30499	2000	2000			214	314	404
43	31599	1000	1000	Minimum deductible is 2000/2000	2000	215	315	405
44	40199	2000	2000	Minimum deductible is 3000/3000	3000	216	316	406
45						231	331	501
46						232	332	502
47						233	333	503
48						234	334	504
49						235	335	505
50						236	336	506
Sheet1

Cell Formulas
Range	Formula
H39	=IF(I39="","","Minimum deductible is "&I39&"/"&I39)
I39	{=IF(OR(F39<500,G39<500),500,IF(SUM(IF(ISNUMBER(SEARCH(LEFT(E39,3),$J$39:$L$50)),1))>0, INDEX($J$38:$L$38,SUMPRODUCT(COLUMN($A$38:$C$38)*--($J$39:$L$50=LEFT(E39,3)))),""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[JayBurn]

You're a step ahead of me Alan, nicely done.

 

[Tennisguuy]

Thanks guys that worked perfectly. I have another situation with this same data that I need help but not sure if I should start another post. If I should let me know. I know I can do a pivot table but a pivot table will not work in this situation

In this same chart there is another column D where I have the state and Column H where I have the cost. I know I can use sumifs and countif to count the cells but variables for the state will always be different. I was not sure how to do sumif where the variable changes. Below is an example of the data

State Code Comp Coll Cost
NY 7398 500 1000 10,000
PA 21199 1000 1000 20,000
LA 31199 2000 2000 30,000
NY 31199 2000 3000 40,000

What I want to do is count the number codes by state and if possible sum the cost.

The state values start cell A38:A60, the code is in E38:E60 and cost is in H38:H60.

I want to put the results in another chart with the following heading in

State Code Cost
NY 2 50,000
PA 1 20,000
LA 1 30,000

The chart will start at A30:C30

Let me know if I should start I new post.

Thanks

 

[JayBurn]

Blah

 

[Tennisguuy]

I did some research on site and I was able to get the count to work. I used the following formula in cell B31 and copied down IF(COUNTIFS($A$38:A38,A38,$E$38:E38,E38),COUNTIFS(A:A,A38,E:E,E38)) and it worked but now not sure how to get the states that are listed to begin in cell A31

 

[Tennisguuy]

Spoke to soon. That kind of worked, it counted the number of codes that were the same not by state.

 

[AlanY]

try this, extend ranges to suit

Excel 2013/2016
	A	B	C	D	E	F	G	H
29	State	Code	Cost
30	NY	2	50,000
31	PA	1	20,000
32	LA	1	30,000
33
34
35
36
37				State	Code	Comp	Coll	Cost
38				NY	7398	500	1000	10,000
39				PA	21199	1000	1000	20,000
40				LA	31199	2000	2000	30,000
41				NY	31199	2000	3000	40,000
Sheet1

Cell Formulas
Range	Formula
B30	=COUNTIF($D$38:$D$41,A30)
C30	=SUMIF($D$38:$D$41,A30,$H$38:$H$41)
A30	{=INDEX($D$38:$D$41, MATCH(0, COUNTIF($A$29:A29, $D$38:$D$41), 0))}
Press CTRL+SHIFT+ENTER to enter array formulas.