Nested If formula with an AND condition

[adrienne0914]

Hi all,

I'm trying to group data based on volume. I'm grouping as follows:

A: >2000
B: 1000-2000
C: 500-999
D: 100-499
E: 1-99​

It looks like this:

[TABLE="width: 600"]
<tbody>[TR]
[TD]Company[/TD]
[TD="align: center"]FY 2017[/TD]
[TD="align: center"]YTD 2017[/TD]
[TD="align: center"]YTD 2018[/TD]
[TD="align: center"]Group[/TD]
[/TR]
[TR]
[TD]ABC Company[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]DEF Company[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]GHI Company[/TD]
[TD="align: center"]2,489[/TD]
[TD="align: center"]1,136[/TD]
[TD="align: center"]945[/TD]
[TD="align: center"]A[/TD]
[/TR]
[TR]
[TD]JKL Company[/TD]
[TD="align: center"]1,025[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]485[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]VH1 Company[/TD]
[TD="align: center"]536[/TD]
[TD="align: center"]169[/TD]
[TD="align: center"]101[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]CBS Company[/TD]
[TD="align: center"]155[/TD]
[TD="align: center"]75[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]DEC Company[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]F[/TD]
[/TR]
</tbody>[/TABLE]

My formula is:
=IF(B2>2000,"A",IF(B2>=1000,"B",IF(B2>=500,"C",IF(B2>=100,"D",IF(B2>0,"E", IF(B2=0,"F",IF(D2=0,"F")))))))

I'm trying to weed out the ones with no activity in 2017 or 2018, making them group F. The way my formula reads now, it assigns group F if B or D equal 0. I don't want DEC Company to be F because there was an increase in activity from 2017 to 2018.

I want the formula to assign group F if B AND D equal 0. Not sure how to insert an AND formula.

Thanks in advance!

 

[jproffer]

=IF(B2>2000,"A",IF(B2>=1000,"B",IF(B2>=500,"C",IF(B2>=100,"D",IF(B2>0,"E", IF(AND(B2=0,D2=0),"F","ALL FALSE"))))))

Not sure if I counted the parenthesis right...

You should never see "ALL FALSE", but I had to put something there . If you want, change it to "" (nothing).

 

[adrienne0914]

=IF(B2>2000,"A",IF(B2>=1000,"B",IF(B2>=500,"C",IF(B2>=100,"D",IF(B2>0,"E", IF(AND(B2=0,D2=0),"F","ALL FALSE"))))))

Not sure if I counted the parenthesis right...

You should never see "ALL FALSE", but I had to put something there . If you want, change it to "" (nothing).

Hi jproffer! You had the parentheses right. This worked like a charm. BUT now I realize I need something slightly different. I don't want to have blanks in any cells. So is there a way to modify the formula so that it assigns a value based on D2 if it is greater than 0 and B2 is 0?

 

[jproffer]

Sure...try this one:

=IF(B2>2000,"A",IF(B2>=1000,"B",IF(B2>=500,"C",IF(B2>=100,"D",IF(B2>0,"E",IF(AND(B2=0,D2>0),D2,IF(AND(B2=0,D2=0),"F","")))))))

 

[adrienne0914]

Sure...try this one:

=IF(B2>2000,"A",IF(B2>=1000,"B",IF(B2>=500,"C",IF(B2>=100,"D",IF(B2>0,"E",IF(AND(B2=0,D2>0),D2,IF(AND(B2=0,D2=0),"F","")))))))

Thanks again! It's assigning the value of D rather than giving it a ranking. I want it to look at D, and assign a group based on that number. So for DEC Company above, the ranking would be E since it's 11. That may be too much nesting for this formula, no?

 

[jproffer]

Ohhhhh, I thought you wanted the value OF D if B was empty....sorry. Not sure if it's too many ifs or not, I doubt it...give me a few

 

[jtakw]

Hi,

Sorry jproffer for stepping in, think that's going to be a "bunch" of IFs, here's my suggestion.

E2 formula uses Table G1:I6 (when your parameters change, just change it in the table).
F2 formula has Table hardcoded (when your parameters change, you'll have to update the formula, but you don't need G1:I6).

Book1
	A	B	C	D	E	F	G	H	I
1	Company	FY 2017	YTD 2017	YTD 2018	Group	Group	0	0	F
2	ABC Company	40	12	12	E	E	1	99	E
3	DEF Company	0	0	0	F	F	100	499	D
4	GHI Company	2,489	1,136	945	A	A	500	999	C
5	JKL Company	1,025	250	485	B	B	1000	2000	B
6	VH1 Company	536	169	101	C	C	2001		A
7	CBS Company	155	75	77	D	D
8	DEC Company	0	0	11	E	E
Sheet69

Cell Formulas
Range	Formula
E2	=LOOKUP(IF(B2=0,D2,B2),G$1:G$6,I$1:I$6)
F2	=LOOKUP(IF(B2=0,D2,B2),{0,1,100,500,1000,2001},{"F","E","D","C","B","A"})

Either way, formula copied down.

 

[jproffer]

I think this is what you want...and it wasn't too many nested IFs in Excel 16

=IF(AND(B2=0,D2=0),"F",IF(SUM(B2,D2)<>D2,IF(B2>2000,"A",IF(B2>1000,"B",IF(B2>500,"C",IF(B2>100,"D",IF(B2>0,"E"))))),IF(D2>2000,"A",IF(D2>1000,"B",IF(D2>500,"C",IF(D2>100,"D",IF(D2>0,"E","")))))))

Don't be sorry at all. I like it...it was indeed a bunch of IFs...yours is MUCH shorter

 

[jtakw]

Cheers jproffer, I think since Excel 2007, you can nest up to 64 levels of IFs

 

[jproffer]

I wasn't sure of the number. I was thinking it was 7 before 2007 version, and I had heard they raised it (finally lol), but wasn't sure how far they raised it.