Excel Averageif help 2

[MJV8198]

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

 

[SkipVought]

hi,

[pre]
Average $1,305
A & B Store Average $1,904 =AVERAGEIF(B$5:B$3633,[highlight #FCE94F]">="&[/highlight]AVERAGE(B$5:B$3633),B$5:B$3633)
C &D Store Average $856 =AVERAGEIF(B$5:B$3633,[highlight #FCE94F]"<"&[/highlight]AVERAGE(B$5:B$3633),B$5:B$3633)
[/pre]

However, I like SUMPRODUCT better cuz it's more intuitive...
[tt]
=SUMPRODUCT((B$5:B$3633>=AVERAGE(B$5:B$3633))*(B$5:B$3633))/SUMPRODUCT(--(B$5:B$3633>=AVERAGE(B$5:B$3633)))
=SUMPRODUCT((B$5:B$3633<AVERAGE(B$5:B$3633))*(B$5:B$3633))/SUMPRODUCT(--(B$5:B$3633<AVERAGE(B$5:B$3633)))
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

Here's something else I'd most certainly do.

In days of olde, with paper, pencil and adding machines, it seemed natural to put totals at the bottom of the page, since that's where you were, punching in all those columns of numbers.

But NOW, in the age of computers and spreadsheets, you can put those aggregations in a much more beneficial and conspicuous place, like "right down in the front row," or right at the TOP!

[pre]
Grand Total $4,736,320
Store Count $3,629
Average $1,305
A & B Store Average $1,904
C & D Store Average $856
Sum of POS Sales
Store Number Sales $ Store Rank
1247 $6,580
4332 $6,432
...
[/pre]

Furthermore, I'd make the TABLE a Structured Table via Insert > Tables > Table. I named your table tSTOR. you can ADD or DELETE stores without worrying about changing the reference range.

The FORMULAE now contain the ST Format...
[pre]
Grand Total =SUM(tSTOR[Sales $])
Store Count =COUNT(tSTOR[Sales $])
Average =AVERAGE(tSTOR[Sales $])
A & B Store Average =AVERAGEIF(tSTOR[Sales $],">="&AVERAGE(tSTOR[Sales $]),tSTOR[Sales $])
C & D Store Average =AVERAGEIF(tSTOR[Sales $],"<"&AVERAGE(tSTOR[Sales $]),tSTOR[Sales $])

[/pre]



Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MJV8198]

Hello Skip,

Thank you very much for your help. You have been kind enough to help in the past and I always learn from your solutions.

Thank you for taking the time to discuss tables! I will figure out a way to use them, not changing references when adding and deleting stores will be beneficial in the long run.

Michael