Excel Array Formula - #N/A error message on Average formula in some rows

[narof]

Hello,

Problem
I have an array formula that is returning a #N/A error on some categories only, that I don't know why.

Data Layout
My data is arranged like this;

[TABLE="width: 500"]
<tbody>[TR]
[TD]Store[/TD]
[TD]Category[/TD]
[TD]Revenue[/TD]
[TD]Profit[/TD]
[TD]Margin %[/TD]
[TD]SOH[/TD]
[TD]Space m2[/TD]
[TD]GMROF[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Pain[/TD]
[TD]$100[/TD]
[TD]$50[/TD]
[TD]50%[/TD]
[TD]$1000[/TD]
[TD]2.5[/TD]
[TD]$20[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Pain[/TD]
[TD]$200[/TD]
[TD]$115[/TD]
[TD]57.5%[/TD]
[TD]$2000[/TD]
[TD]3.0[/TD]
[TD]$38[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Pain[/TD]
[TD]$125[/TD]
[TD]$80[/TD]
[TD]64%[/TD]
[TD]$1250[/TD]
[TD]2.2[/TD]
[TD]$36[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Pain[/TD]
[TD]$240[/TD]
[TD]$160[/TD]
[TD]66%[/TD]
[TD]$2150[/TD]
[TD]3.0[/TD]
[TD]$53[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Pain[/TD]
[TD]$155[/TD]
[TD]$95[/TD]
[TD]61%[/TD]
[TD]$1750[/TD]
[TD]2.8[/TD]
[TD]$34[/TD]
[/TR]
[TR]
[TD]Top 20[/TD]
[TD]Pain[/TD]
[TD]xxx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Relevant information

• 45 different categories (i.e. Pain, Cough etc..)
• 40+ stores
• Data is in a data table

Formula Outcome

I want to calculate the average of the Top 20% for each of the 45 categories (based on the stores with the highest GMROF for each category) and calculate this for each column: Revenue, Profit etc..

The slow way is to sort the pain category by GMROF, add a row, sort highest to lowest, write a average formula, then calculate the average of the Top 20% (8.4 stores, rounded to 8 stores) of the stores for each column.

Formula that works
{=AVERAGE(LOOKUP(RIGHT(LARGE(IF(Table2[CATEGORY]=$C1489,INT(Table2[GMROF])+ROW(Table2[GMROF])%%),ROW(INDIRECT("1:"&ROUND($L$1*COUNTIF(Table2[CATEGORY],$C1489),0)))),4)+0,ROW(Table2[CATEGORY]),Table2[Sales]))}


Formula returning a #N/A message
{=AVERAGE(LOOKUP(RIGHT(LARGE(IF(Table2[CATEGORY]=$C1490,INT(Table2[GMROF])+ROW(Table2[GMROF])%%),ROW(INDIRECT("1:"&ROUND($L$1*COUNTIF(Table2[CATEGORY],$C1490),0)))),4)+0,ROW(Table2[CATEGORY]),Table2[Sales]))}



Note: $L$1 = 20% (format in percentage)


Your assistance with this would be appreciated.

Cheers,

Narof

 

[mfexcel]

A pivot table should do the job in a more effective and efficient way.

 

[Luchano]

Have you checked for trailing spaces?
It is kinda hard to diagnose the problem when there's no sample data and/or where the error occurs.

 

[narof]

Can I upload a sample?

 

[narof]

Pivot table is an interesting idea, how would I return the average of the Top 20% based on GMROF in the Pivot Table?

 

[mfexcel]

Create Pivot Table based on your data table
I assume you would have the following layout:
1) Category, Store in Row Label
2) Under Store label, you may apply filter: Top 20 --> Percent --> Based on the value you want
3) Set the Subtotal field to "Average"

Reminder: Use "Calculated Field" to show Margin instead of using the original data.

Hope it helps.

Pivot table is an interesting idea, how would I return the average of the Top 20% based on GMROF in the Pivot Table?