Calculation error when a cell is $0

[mf1]

Hello,

Outcome

• Calculate the average of the Top 20% of each category, based on the ranking of GMROF (highest to lowest)
• Use this formula for Sales, GP$, Stock, m2

Problem

• Calculation error appears when GMROF for a category is $0

I have attached an example of my working spreadsheet, with the Top 20% table in green.


Many thanks.

 

[Hui]

P2: =AVERAGE(IFERROR(LOOKUP(RIGHT(LARGE(IF(CatData[CATEGORY]=[@CATEGORY],INT(CatData[GMROF])+ROW(CatData[GMROF])%%),ROW(INDIRECT("1:"&ROUND($K$1*COUNTIF(CatData[CATEGORY],[@CATEGORY]),0)))),4)+0,ROW(CatData[CATEGORY]),CatData[Sales]),0)) Array enter that using Ctrl+Shift+Enter

Adjust for other columns

ps: Nice formula

 

[mf1]

Ah, the iferror, never thought of that.

Thanks Hui, keep up the great work!

 

[mf1]

Hui,

I am getting an error with the counts of rows, resulting in the iferror formula reporting them as zero, however it still counts the rows in the average, resulting in understating the average. (see attached some screen shots of the formula)

Answer should be $349,538 not $304,989.

Your assistance would be appreciated.

Thanks

 

[Hui]

That's going to take a bit more thinking
Have you looked at any Averageifs() functions?

 

[mf1]

I looked at that however I don't know it throwing up an error, as there is data in the cells

 

[Hui]

Have a look at this Averageifs solution
I do this so I can check it manually first