Excel to recommend 8 "Price Groups" from range of prices

[sanantonio]

Hi All,

Pre-amble:
Thanks for taking the time to read my requirement. I'm convinced there's probably a real simple way of doing this but I've sat down to try and put something together a couple of times and I'm all googled out. Up to this point I've been using quartile and a kind of manual game of upping and downing. It's a massive time suction on my day for something so trivial.

INPUT:
I work with different data sets from different businesses. I get a raw range of approx 1,000+ items with their sale price. Example:

Sometimes you'll get a data set and the sales prices range between $0.49 and $9.99. Sometimes $1.00 and $99, sometimes $0.49 and $999.99.

OUTPUT:
I need to create exactly 8 "Price Groups", ideally with roughly the same amount of items in each.

For example:

(Price groups don't necessarily have to be named automatically, but maybe something like this:

(Or any combination really)

ASK:

How can I get excel to recommend price groupings to be automatically?

Can be a formulaic or VBA solution but I really need something to save me multiple hours a week with this task. Thanks!

 

[StephenCrump]

You could have 8 bands of virtually identical size if you don't mind price ranges like:

$0.00 to $2.33
$2.34 to $7.55 etc

But I'm guessing you'd prefer whole dollar bands, e.g

$0.00 to $2.99
$3.00 to $5.99?

You could use Solver ...

I set up the initial guess below, based on 8 bands roughly equally spaced between $0 and $20

	A	B	C	D	E
1	Prices		Band		Count
2	7.25		0	$0.00 - $1.99	7
3	16.90		2	$2.00 - $4.99	16
4	3.25		5	$5.00 - $7.99	15
5	19.90		8	$8.00 - $10.99	10
6	5.90		11	$11.00 - $13.99	14
7	4.25		14	$14.00 - $16.99	22
8	14.99		17	$17.00 - $19.99	13
9	3.00		20	$20.00 +	3
10	11.90				100
11	19.25
12	13.00			Var(Count)	34.0
13	7.90
14	5.00
15	4.99
16	18.25
17	4.99
18	1.25
19	8.90
20	10.00
21	1.50
22	13.25
23	19.00
24	9.50
25	16.25
26	19.99
27	18.00
28	5.99
29	10.99
30	11.25
31	5.25
32	16.50
33	3.50
34	4.90
35	18.90
36	14.50
37	15.90
38	19.25
39	10.90
40	11.50
41	18.00
42	10.00
43	12.99
44	20.00
45	14.99
46	15.99
47	15.90
48	12.50
49	16.25
50	11.00
51	7.90
52	14.99
53	19.99
54	12.00
55	3.00
56	1.90
57	14.90
58	1.99
59	14.25
60	15.90
61	15.99
62	11.50
63	6.00
64	2.00
65	9.25
66	10.99
67	14.00
68	17.25
69	13.50
70	2.50
71	1.50
72	20.00
73	14.25
74	13.99
75	4.50
76	3.90
77	7.25
78	11.99
79	5.90
80	1.00
81	6.99
82	2.99
83	16.99
84	16.99
85	11.00
86	17.99
87	4.25
88	6.50
89	9.00
90	2.99
91	7.99
92	6.25
93	2.00
94	16.99
95	8.00
96	6.25
97	16.25
98	19.25
99	20.25
100	1.25
101	16.50
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=TEXT(C2,"$0.00")&" - " & TEXT(C3-0.01,"$0.00")
D9	D9	=TEXT(C9,"$0.00")&" +"
E2:E8	E2	=COUNTIFS(A$2:A$101,">="&C2,A$2:A$101,"<"&C3)
E9	E9	=COUNTIFS(A$2:A$101,">="&C9)
E10	E10	=SUM(E2:E9)
E12	E12	=VAR(E2:E9)

Named Ranges
Name	Refers To	Cells
solver_adj	=Sheet5!$C$2:$C$9	D2:E2
solver_lhs1	=Sheet5!$C$2:$C$9	D2:E2

Solver provided this solution:

Band		Count
0	$0.00 - $3.99	17
4	$4.00 - $5.99	11
6	$6.00 - $8.99	12
9	$9.00 - $11.99	15
12	$12.00 - $14.99	15
15	$15.00 - $16.99	14
17	$17.00 - $18.99	6
19	$19.00 +	10
		100
	Var(Count)	12.3

This looks like about the best you can do with this sample data and whole dollar increments. It would be relatively simple to adapt if you were happy to vary the increment, e.g. from $1.00 to $0.50, say.

 

[NdNoviceHlp]

Hi sanantonio. You can trial this. HTH. Dave

Sub Group8()
'Sales prices in B2 to whatever
'output 8 price groups in E2 to whatever; No. of items in F2 to whatever
Dim LastRow As Integer, Arr As Variant, Low As Double, High As Double, Interval As Integer
Dim Cnt As Integer, Cnt2 As Integer, Cnt3 As Integer, i As Integer, Rng As Range
'****change sheet name to suit
With Sheets("Sheet1")
LastRow = .Range("B" & .Rows.Count).End(xlUp).Row
Set Rng = .Range("B2:B" & LastRow)
'change rng to array
Arr = Application.WorksheetFunction.Transpose(Rng)
'get no. of items pefr group
Interval = Round((LastRow - 1) / 8)
'output set up/ clear previous results
.Range("E" & 1).Value = "Price Group"
.Range("F" & 1).Value = "Number of Items"
.Range(.Cells(2, "E"), .Cells(9, "F")).ClearContents
'get lowest price for group
Low = Arr(GetMin(Arr))
'create 8 price groups
For Cnt = 2 To 9
'loop each interval
For Cnt2 = 1 To Interval
    'remove interval number of items from array
    For i = GetMin(Arr) + 1 To UBound(Arr) ' - 1
    Arr(i - 1) = Arr(i)
    Next i
    ReDim Preserve Arr(UBound(Arr) - 1)
Next Cnt2
'get highest remaining value for group
High = Arr(GetMin(Arr))
'output group price range
.Range("E" & Cnt).Value = Format(Low, "currency") & " - " & Format(High, "currency")
'output number ot items in each range
For Cnt3 = 1 To LastRow
If .Range("B" & Cnt3).Value >= Low And .Range("B" & Cnt3).Value <= High Then
.Range("F" & Cnt).Value = .Range("F" & Cnt).Value + 1
End If
Next Cnt3
'set low for next group
Low = High + 0.01
Next Cnt
End With
End Sub

Function GetMin(Tarr As Variant) As Integer
'returns array posn of smallest value
Dim Cnt As Integer, TempCnt As Integer, mini As Double
mini = Tarr(LBound(Tarr))
For Cnt = LBound(Tarr) To UBound(Tarr) - 1
If Tarr(Cnt) <= mini Then
mini = Tarr(Cnt)
TempCnt = Cnt
End If
Next Cnt
GetMin = TempCnt
End Function

Edit: I see that Stephen has already provided something likely better. It was fun anyways. Dave

 

[sanantonio]

You could have 8 bands of virtually identical size if you don't mind price ranges like:

$0.00 to $2.33
$2.34 to $7.55 etc

But I'm guessing you'd prefer whole dollar bands, e.g

$0.00 to $2.99
$3.00 to $5.99?

You could use Solver ...

I set up the initial guess below, based on 8 bands roughly equally spaced between $0 and $20

	A	B	C	D	E
1	Prices		Band		Count
2	7.25		0	$0.00 - $1.99	7
3	16.90		2	$2.00 - $4.99	16
4	3.25		5	$5.00 - $7.99	15
5	19.90		8	$8.00 - $10.99	10
6	5.90		11	$11.00 - $13.99	14
7	4.25		14	$14.00 - $16.99	22
8	14.99		17	$17.00 - $19.99	13
9	3.00		20	$20.00 +	3
10	11.90				100
11	19.25
12	13.00			Var(Count)	34.0
13	7.90
14	5.00
15	4.99
16	18.25
17	4.99
18	1.25
19	8.90
20	10.00
21	1.50
22	13.25
23	19.00
24	9.50
25	16.25
26	19.99
27	18.00
28	5.99
29	10.99
30	11.25
31	5.25
32	16.50
33	3.50
34	4.90
35	18.90
36	14.50
37	15.90
38	19.25
39	10.90
40	11.50
41	18.00
42	10.00
43	12.99
44	20.00
45	14.99
46	15.99
47	15.90
48	12.50
49	16.25
50	11.00
51	7.90
52	14.99
53	19.99
54	12.00
55	3.00
56	1.90
57	14.90
58	1.99
59	14.25
60	15.90
61	15.99
62	11.50
63	6.00
64	2.00
65	9.25
66	10.99
67	14.00
68	17.25
69	13.50
70	2.50
71	1.50
72	20.00
73	14.25
74	13.99
75	4.50
76	3.90
77	7.25
78	11.99
79	5.90
80	1.00
81	6.99
82	2.99
83	16.99
84	16.99
85	11.00
86	17.99
87	4.25
88	6.50
89	9.00
90	2.99
91	7.99
92	6.25
93	2.00
94	16.99
95	8.00
96	6.25
97	16.25
98	19.25
99	20.25
100	1.25
101	16.50
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=TEXT(C2,"$0.00")&" - " & TEXT(C3-0.01,"$0.00")
D9	D9	=TEXT(C9,"$0.00")&" +"
E2:E8	E2	=COUNTIFS(A$2:A$101,">="&C2,A$2:A$101,"<"&C3)
E9	E9	=COUNTIFS(A$2:A$101,">="&C9)
E10	E10	=SUM(E2:E9)
E12	E12	=VAR(E2:E9)

Named Ranges
Name	Refers To	Cells
solver_adj	=Sheet5!$C$2:$C$9	D2:E2
solver_lhs1	=Sheet5!$C$2:$C$9	D2:E2

Solver provided this solution:

Band		Count
0	$0.00 - $3.99	17
4	$4.00 - $5.99	11
6	$6.00 - $8.99	12
9	$9.00 - $11.99	15
12	$12.00 - $14.99	15
15	$15.00 - $16.99	14
17	$17.00 - $18.99	6
19	$19.00 +	10
		100
	Var(Count)	12.3

This looks like about the best you can do with this sample data and whole dollar increments. It would be relatively simple to adapt if you were happy to vary the increment, e.g. from $1.00 to $0.50, say.

View attachment 121543

Hi Stephen,

Thanks for coming back to me.

I haven't heard of "Solver" before? I've just checked my add in list and it's there. I've added it but can't seem to find it within my excel, I'll do some youtubing and see if I can fathom it out.

You're solution looks great, but how are the values in Column C populated? Is this what this "Solver" does?

 

[NdNoviceHlp]

Could you post some sample data to see what, if anything, went wrong with the code I posted? It seemed to work with my limited data mock up. Dave

 

[sanantonio]

Could you post some sample data to see what, if anything, went wrong with the code I posted? It seemed to work with my limited data mock up. Dave

Hi Dave,

Thanks for your response. I hadn't tried your code just yet, was replying to the previous solution.

I've just tried the VBA and it works perfectly! I get a debug if there are any none numbers here:

But it still generates the result okay, so I've thrown an on error resume next in there and it seems to be all good.

Thank you very much!

 

[sanantonio]

Ignore me, sorted it! Thanks again.

 

[NdNoviceHlp]

You are welcome sanantonio. I'm hoping you're relieved by the disappearance of the time suction. I'm not quite sure what you meant by "Ignore me, sorted it! "... was there an error that needs to be addressed? Dave

 

[sanantonio]

Oh for sure! It's why I love this forum so much, you've saved me countless man hours over the past few years!

And no issues with the VBA at all, I had posted that it was only returning 7 ranges, not 8, but it seems to return 7 and hit debug if column B has anything other than numbers in it. So I've added a step that removes none numbers before running the VBA!

 

[NdNoviceHlp]

Thanks for the update and I'm glad you got it sorted out. I hadn't considered the possibility of non-numeric data. Have a nice day. Dave