Help in Rank function

[thomas_skariah]

Hi All,

I have a pivot table using rank function to display top 15 brands country wise.

Here the problem I'm facing is some brand is not present in some country lik eg :-Aus doent have brand C which comes under rank 10 (See rank 10 not present in excel file)

I have to display 15 brands for each country, but because of some brands not present say  Aus it showing only 13 instead of 15.

Can anyone suggest on this.

Attached is the sample excel file.

Formula for Rank:-=aggr(rank(sum([SALES])),BRAND)

Formula for Brand=if(aggr(rank(sum([SALES])),BRAND)<=15,BRAND)

Regards,

Tom

[#2]

Add Country to your aggregation. That should be fine.

[thomas_skariah]

Hi ,

Is there any way that if any of the countries have have brand less than 15, then also it should show 15 rows.With the rest of the row keep as blank?

Regards,

Tom

[MK_QSL]

Your sample file is wrong.

how come AUS, Brand A is coming twice?

[thomas_skariah]

Hi Manish,

I have mentioned only the initials .So its two different brand with same initials.Sorry for confusion.

Regards,

Tom

[#6]

You will have to address this at the script to insert a dummy record with a 0 value

Sent from my iPhone