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:

Company	FY 2017	YTD 2017	YTD 2018	Group
ABC Company	40	12	12	E
DEF Company	0	0	0	F
GHI Company	2,489	1,136	945	A
JKL Company	1,025	250	485	B
VH1 Company	536	169	101	C
CBS Company	155	75	77	D
DEC Company	0	0	11	F

<tbody>
</tbody>

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet69​

Worksheet Formulas Cell 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"}) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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.