I have a list of sales $ by store for 3200 stores. I am trying to Rank each store A, B, C, D based on sales level. I want to take the overall average of all 3200 stores, the stores above that level are A or B stores, the store below that level are C or D stores. Once I have the overall average I want to look get the average sales above the overall average and create an average of the A and B stores and the Stores above the AB average become the "A" stores and below the Average AB become the "B" Stores. I would do a similar calculation for the c and D stores. I have setup my spread sheet as follows. The rank will be determined by a separate formula. I have included a test file.
[/indent] Column A [/indent] Column B
[/indent] Store Number , Store Sales $, Rank
1
2
3
.
.
.
3200
3201 =average(B1:B3200) Overall Store Average
3202 =averageif(B1:$3200,">B3201",B1:B3200) To find Average of the A&B Stores
3203 =averageif(B1:B3200, "<B3201",B1:3200) To Find Average of the C&D stores
I update the information weekly and check for changes. The actual data is in a pivot table but the test data in the file is not in a pivot table and I get an error.
Thank you for your Help
Michael
[/indent] Column A [/indent] Column B
[/indent] Store Number , Store Sales $, Rank
1
2
3
.
.
.
3200
3201 =average(B1:B3200) Overall Store Average
3202 =averageif(B1:$3200,">B3201",B1:B3200) To find Average of the A&B Stores
3203 =averageif(B1:B3200, "<B3201",B1:3200) To Find Average of the C&D stores
I update the information weekly and check for changes. The actual data is in a pivot table but the test data in the file is not in a pivot table and I get an error.
Thank you for your Help
Michael