Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.
One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.
Course Of The Month
10 days, 5 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Rank percentile from list of data
Posted on 2013-11-08
Hi,
I have a column of data, some rows are 0, some are not.
I want some formula that will sum the data and return the data amount that falls in the top 1% of data, what formula could achieve this?
Many thanks
I have a column of data, some rows are 0, some are not.
I want some formula that will sum the data and return the data amount that falls in the top 1% of data, what formula could achieve this?
Many thanks
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
2 Comments
Active today
Hi
you could enter an Array formula like this
Regards
you could enter an Array formula like this
=PERCENTILE.INC(IF(A2:A5<>0,A2:A5),0.99)
Regards
Hello Seamus2626,
Perhaps Rgonzo's suggestion will work for you but I think it only gives you the cutoff point. If you want to sum everything above that point as you say then you need to use the percentile within a SUMIF fomula, e.g. if data is in A2:A1000 try
=SUMIF(A2:A1000,">="&PERCE
confirmed with CTRL+SHIFT+ENTER
That assumes you want to exclude zeroes from the calculation.....but you didn't say that, so if zeroes should be included it's just
=SUMIF(A2:A1000,">="&PERCE
which can be entered normally
regards, barry
Perhaps Rgonzo's suggestion will work for you but I think it only gives you the cutoff point. If you want to sum everything above that point as you say then you need to use the percentile within a SUMIF fomula, e.g. if data is in A2:A1000 try
=SUMIF(A2:A1000,">="&PERCE
confirmed with CTRL+SHIFT+ENTER
That assumes you want to exclude zeroes from the calculation.....but you didn't say that, so if zeroes should be included it's just
=SUMIF(A2:A1000,">="&PERCE
which can be entered normally
regards, barry
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210 (2 * 3 * 5 * 7) or 2310 (2 * 3 * 5 * 7 * 11).
The larger templa…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month10 days, 5 hours left to enroll
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.