Using Excel 2010.
I have a spreadsheet with a page of data and a page to create charts. On the charts page I need to create a list of failures. I need the failure that occurs the most, then in the cell below that the 2nd highest failure occurrance and so on down the list until I have the top ten occurring failures. Also in the column to the right of that, a count for each failure.
Thank you very much.
DC
Can you not just sort by the number of failures?
Entia non sunt multiplicanda sine necessitate
I need to pull in top 10 causes and how many of each to create charts.
Got that. So why not just sort by number of failures?
the simplest "non-freaky-formula" approach is to put off to the right on your charts page a list of all the possible causes. That can be derived from the original data quickly using an ADVANCED FILTER on that column, unique values only, copy to another location.
Assuming that list is now in column X starting at X2, in Y2 you put a COUNTIF() formula to count how many of that X2 item exists on the other sheets criteria column.
Once you have your X=causes and Y=counts for each cause, in Z you do a RANK() formula to order them.
Now back in your chart you can list 1-10 in A1-A10, then do an INDEX/MATCH on the ranked values to pull over the causes and counts into your top-ten listing.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks