Can you get a percent range in Excel?
I have a lengthy Excel spreadsheet for email statistics over a few years. I am measuring average opens/clicks/etc. But I would also like to put in a specific range. I know there's a minimum/maximum formula, but how can I put up the 20%-80% range.
NOTE: after entering rows and rows of data, I am pulling up everything in pivot tables for better comparisons, so I would like to also pull up the 20-80 there as well.
Additionally, I am not using VB (mainly just because I'm not familiar with it) so please minimize VB responses.
I am going on the basis that you have a column with percentages that you want to work with. You can create a new column called specific range for example. You can then enter the below formula to work from, you will have to change the cell range to suit your needs.
here is a formula that will determine if the email falls into the 20-80% category:
=IF(AND(A1>=20%, A1<=80%),"True", "False")
Hope this helps.
I don't know if you can do it directly in a pivot table, but you could use the Excel PERCENTILE function to get the boundaries of the 20th and 80th percentile.
For the pivot table if you add columns to your source data that use the PERCENTILE function you could then add those columns to your pivot table. It wouldn;t matter what calculation you used in the pivot table as the values would be the same for all rows.