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!

 

[adrienne0914]

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

I love this forum! This is EXACTLY what I needed. I have Excel 2013, and thought there was a 7 nest limit. I'm glad to know there isn't. Thanks again!!

 

[adrienne0914]

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).

	A	B	C	D	E	F	G	H	I
Company	FY 2017	YTD 2017	YTD 2018	Group	Group	F
ABC Company	E	E	E
DEF Company	F	F	D
GHI Company	A	A	C
JKL Company	B	B	B
VH1 Company	C	C	A
CBS Company	D	D
DEC Company	E	E

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]40[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]99[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: right"]100[/TD]
[TD="align: right"]499[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2,489[/TD]
[TD="align: right"]1,136[/TD]
[TD="align: right"]945[/TD]

[TD="align: right"]500[/TD]
[TD="align: right"]999[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1,025[/TD]
[TD="align: right"]250[/TD]
[TD="align: right"]485[/TD]

[TD="align: right"]1000[/TD]
[TD="align: right"]2000[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]536[/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]101[/TD]

[TD="align: right"]2001[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]155[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]77[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet69​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]=LOOKUP(IF(B2=0,D2,B2),G$1:G$6,I$1:I$6)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=LOOKUP(IF(B2=0,D2,B2),{0,1,100,500,1000,2001},{"F","E","D","C","B","A"})[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Either way, formula copied down.

This is great! The grouping numbers did actually change. So this will speed up the process in the future. Thanks!

 

[jtakw]

This is great! The grouping numbers did actually change. So this will speed up the process in the future. Thanks!

You're welcome, glad to help.