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
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
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