Does anyone know how to adjust this formula to ignore blanks and return results?
The formula does great but when filtered and there is blanks....it returns #N/A instead of results.
Please Login or Register to view this content.
anyone???? In dire need of help. I'm stuck
Hi Shelly,
I'd bet a pivot table could do this without needing a formula.
Also there is a Top 10 Conditional Format tool.
If you attach a sample it would be easy to try other methods. To attach a sample, click on "Go Advanced" and then the paper clip icon above the advanced message area.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Post a SMALL sample file and show us what results you expect.
Emphasis on SMALL!
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Hi Guys...
I've attached the sample... I've created separate tables for each pivot. In the tables I wrote the formula to pull the top 10 then I create a pivot with the top 10 so when I refresh the only results I get is the top 10 results for each pivot field. The only problem I am having is that when the pivot tables that the tables pull the data from is filtered it gives the #N/A error.
OK Shelly,
Do this...
In your attached example click in K3 (the pivot table row filter) and then the filter dropdown in K3. Then click the submenu called Value Filter. At the bottom of this submenu is "Top 10..", click it.
This next dialog asks which column you want to filter the top 10 from. Change the last dropdown and it will do all your work for you.
I don't think you need those Array formulas at all if you do as above in the Pivot Table.
I hope that helps.
The tables with the top 10 is being used to create a pivot of only top 10s. The different tables shows the top 10s of every category. Since this report is being generated everyday...there might be different people at the top 10 for each category. The formula really helps howvweer its just that it gives an error when there is blanks.
Sorry, I can't download your file. I have download size limits.Sample.xlsx (349.1 KB, 4 views)
Here try again...shrinked itSample.zip
Hi Shelly,
Use the Pivot Table ONLY to generate the top 10 list for each category. Use your original data and then do multiple pivot tables to show top 10 for each column. This was what I meant.
Ohhhh I see...I will give it a try... Thanks much MarvinP
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks