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;

Store	Category	Revenue	Profit	Margin %	SOH	Space m2	GMROF
1	Pain	$100	$50	50%	$1000	2.5	$20
2	Pain	$200	$115	57.5%	$2000	3.0	$38
3	Pain	$125	$80	64%	$1250	2.2	$36
4	Pain	$240	$160	66%	$2150	3.0	$53
5	Pain	$155	$95	61%	$1750	2.8	$34
Top 20	Pain	xxx	xx	xx	xx	xx	xx

<tbody>
</tbody>

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?