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;
<tbody>
</tbody>
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;
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