If Statement using a range of values?

[Kharyat]

Hello:

Is there any way I can automate the "RANGE" column to use a formula that would return the A/B/C value if it's within that range I'm giving to the right?? I've tried if /or statements but I'm not successful

Loc VALUE %of sales % cumm RANGE
1 $152,503.00 8.37% 8.37% A 60% A
2 $98,546.00 5.41% 13.79% A 25% B
3 $90,546.00 4.97% 18.76% A 15% C
4 $92,352.00 5.07% 23.83% A
5 $89,054.00 4.89% 28.72% A
6 $86,546.00 4.75% 33.47% A
7 $83,287.00 4.57% 38.04% A
8 $78,524.47 4.31% 42.36% A
9 $77,034.84 4.23% 46.59% A
10 $76,619.51 4.21% 50.79% A
11 $75,032.62 4.12% 54.91% A
12 $74,263.10 4.08% 58.99% A
13 $74,109.36 4.07% 63.06% B
14 $73,985.91 4.06% 67.12% B
15 $73,456.00 4.03% 71.16% B
16 $72,691.23 3.99% 75.15% B
17 $72,254.24 3.97% 79.12% B
18 $71,002.11 3.90% 83.01% B
19 $68,465.00 3.76% 86.77% C
20 $67,721.53 3.72% 90.49% C
21 $63,545.00 3.49% 93.98% C
22 $59,546.00 3.27% 97.25% C
23 $50,068.00 2.75% 100.00% C

 

[Scott Huish]

Generally, I would say use a VLOOKUP formula, but I don't understand your table.
The closest value to 25% is 23.83% in your table which says A, why are you returning B?
Same issue with 15% (13.79% is A)

 

[Kharyat]

Generally, I would say use a VLOOKUP formula, but I don't understand your table.
The closest value to 25% is 23.83% in your table which says A, why are you returning B?
Same issue with 15% (13.79% is A)

Hi Scott -

I want to give a RANK (A,B,C) based on the % value of sales. So I want to see how many items are 60% of my sales, the next 25 %, and the next 15% - does this make more sense now?

A 60 % 0 to 60%
B 25 % 60.5 to 85%
c 15 % 85.5 to 100%



Loc VALUE %of sales % cumm
1 $152,503.00 8.37% 8.37% A
2 $98,546.00 5.41% 13.79% A
3 $90,546.00 4.97% 18.76% A
4 $92,352.00 5.07% 23.83% A
5 $89,054.00 4.89% 28.72% A
6 $86,546.00 4.75% 33.47% A
7 $83,287.00 4.57% 38.04% A
8 $78,524.47 4.31% 42.36% A
9 $77,034.84 4.23% 46.59% A
10 $76,619.51 4.21% 50.79% A
11 $75,032.62 4.12% 54.91% A
12 $74,263.10 4.08% 58.99% A
13 $74,109.36 4.07% 63.06% B
14 $73,985.91 4.06% 67.12% B
15 $73,456.00 4.03% 71.16% B
16 $72,691.23 3.99% 75.15% B
17 $72,254.24 3.97% 79.12% B
18 $71,002.11 3.90% 83.01% B
19 $68,465.00 3.76% 86.77% C
20 $67,721.53 3.72% 90.49% C
21 $63,545.00 3.49% 93.98% C
22 $59,546.00 3.27% 97.25% C
23 $50,068.00 2.75% 100.00% C

 

[DutchDisaster]

How about this?

Assuming Loc 1 is in cell A2, then enter this formula in cell E2 and expand down the column:

=IF($D2<0.15;"C";IF(AND($D2>=0.15;$D2<0.25);"B";IF($D2>=0.25;"A";"N/A")))

 

[Kharyat]

Hi DutchDisaster - this is still not working

I just want to simplify this sheet of Pareto review for categorizing sales so I don't have to do it manually which is not a big deal to do but I rather automate.

Kharyat