Well this one gave me plenty of grief!
Two offerings in the attached.
1. On
Sheet1 there's a conditional formatting method:
In column B of that sheet there's conditional formatting for the bottom and top 10% of amounts per day, green and red. The actual formula used for those conditional formats is in cells E2 and F2 (copied down).
In column C there's similar conditional formatting but it looks at he whole month (it would continue to work if there were more than one month's data since it looks at both the month and the year when assessing the data). The formulae used for column C conditional formatting are in columns H & I.
The only problem I had was the conditional formatting didn't seem to be showing up and I was looking in vain for a fault in my formulae. As it turns out there was nothing wrong with the formulae! There's a bug in Excel, it's known of, and on my machine it shows up; a cell which should be highlighted isn't. If you filter a column on colour, it does the filtering correctly but not all cells are highlighted. This is the case filtered or not:
View attachment 81377
Hopefully this doesn't happen on your machine! If it doesn't, you only need column A:C, all the rest can be deleted.
To check the highlighting in column C I copied the amounts to column K and applied the built in top and bottom 10% highlighting:
View attachment 81378
This worked properly and is what alerted me to the bug.
I deleted the blank rows in your data on
Sheet1 because it was playing havoc with the results of the formulae, but I added conditional formatting to column A to help highlight changes in dates. It doesn't work perfectly because you're missing some dates (eg. 18th September).
2. On
Sheet1 (2) there's another solution using
Power Query; two tables: one for he daily tops and bottoms and one for the monthly.
The daily table is more than 20% (216 rows instead of about 200) because I rounded up 10% of the count of rows for each day so that there'd be at least one highlighted row per day.
Both tables will work properly even if there is more than one month's data in the source data.
If you change the data in the blue table (
Table1) you need to refresh the results in each green table (as you would a pivot table) by right-clicking the table and choosing
Refresh (or there's a
Refresh All button in the
Data tab of the ribbon).
This is a more robust solution than conditional formatting.
