Uneducated_Rick
New Member
- Joined
- May 4, 2012
- Messages
- 1
Hi,
I've got a spreadsheet containing my data pulled from various sources and appears to be working fine.
Column C contains data about the region (sometimes a name, sometimes a number - thank you to the person who decided this was acceptable).
Column E contains a KPI value.
What I've been asked is if we can conditionally format column E so that for each region we highlight the top third green, middle third amber and bottom third red.
I tried using the formula =AND(C5=1,F5>PERCENTILE(F$5:F$489,0.33)) but this highlights based on the entire data set, not the regional range. I know I could redefine my range to be the correct length but this means every month having to change all the formulas.
Is there any way to use the percentile command for a defined subset? Data is sorted on column C so could a macro be written with a variable based on the start and end row numbers of the values in column C?
Thanks for any help
p.s. using Excel 2010
I've got a spreadsheet containing my data pulled from various sources and appears to be working fine.
Column C contains data about the region (sometimes a name, sometimes a number - thank you to the person who decided this was acceptable).
Column E contains a KPI value.
What I've been asked is if we can conditionally format column E so that for each region we highlight the top third green, middle third amber and bottom third red.
I tried using the formula =AND(C5=1,F5>PERCENTILE(F$5:F$489,0.33)) but this highlights based on the entire data set, not the regional range. I know I could redefine my range to be the correct length but this means every month having to change all the formulas.
Is there any way to use the percentile command for a defined subset? Data is sorted on column C so could a macro be written with a variable based on the start and end row numbers of the values in column C?
Thanks for any help
p.s. using Excel 2010
