Averaging with N/A?

[santafe]

I think I need an array formula for this, but I haven't been able to figure that part out.
Example: column A has whole number values, column B has percents.
My equation is: =AverageIf(A6:A50,">"&Percentile(B6:B50,0.9))
I'm trying to take the top 10th percentile of the percentage column and return the average of the whole number column, but if the whole number has an N/A then I get the Div/0 error. How do I fix this?

Second Question: column A has whole numbers, column B has percents, column C has word values (Cat, Dog, Bird)
How do I write an equation that will only return the average of column A for only the top 10th percentile (from column B) and only include Cats?

 

[steve the fish]

See if you have aggregate function?

=AVERAGEIFS(A6:A50,B6:B50,">"&AGGREGATE(16,6,B6:B50,0.9))

 

[santafe]

thank you!! This is great.

Any chance you know how to solve the second question?

 

[steve the fish]

It depends. Are you calculating the percentile before you involve the cats or after?

 

[santafe]

I'm trying to say: 1/ only look at the rows labeled Cats, 2/ now only look at the top decile of Column B, of this top decile what's the average of Column A?

 

[steve the fish]

Try one of these which are slightly different:

=AVERAGEIFS(A6:A50,B6:B50,">"&AGGREGATE(16,6,B6:B50,0.9),C6:C50,"Cats")
=AVERAGEIFS(A6:A50,B6:B50,">"&AGGREGATE(16,6,B6:B50/(C6:C50="Cats"),0.9))

Im guesssing you need the 2nd one.

 

[santafe]

This is great, thanks!

 

[steve the fish]

Actually thinking about it this:

=AVERAGEIFS(A6:A50,B6:B50,">"&AGGREGATE(16,6,B6:B50/(C6:C50="Cats"),0.9),C6:C50,"Cats")

This calculates the percentile based on if Cats is in column C firstly. It then produces the average of column A if column B is greater than the percentile number and column C equals Cats.