Top 10 Only Positive Numbers in a Pivot

[Maroota]

Hi All

I hope someone would help with a question.

Is it possible to create a pivot that only shows the top 10 positive numbers?

For example if the data contains negative values the pivot omits these values, and reduces the pivot to say top 8.

I am creating a chart based on the pivot, but don't want to include any negative values.

Many thanks for your time and expertise.

Diane

[sweep]

Hi,

Not sure if you can do this with a pivot table, but assuming your data is in A1:A25, the following array formula in row 1 of any column should give you the top positive numbers.

=IFERROR(LARGE(IF(A1:A25>0,A1:A25,""),ROW()),"")

NB: Use CTRL, SHIFT and ENTER to confirm an array fuction - not just enter.