Hi,
I'm really struggling with this and i hope someone can help.
I have a set of data, and I want to find the average of the values in the series, but I only want the average of the values that are in the bottom quartile of the data AND the values in the adjacent collum must be in the Top Quartile of that Collum - I also want to limit this to an additional variable which is the department the values are in... So In the attached spreadsheet in F1 I need a formula that will look from A4:A23, see that A matches the value in E1... And the see of the values adjacent to an A (1, 4, 5,6,10,11,13,14,15,18,19,20), See that 1,4,5 are in the bottom quartile, and see that the values adjacent are 100,1,99... See that 1 isn't in the upper quartile of the series C4:C23, so eliminate 4, and I have 1 and 5 left - so the average is 6/2 = 3. And in G1 I want the average of the other two numbers (100 and 99 - so 99.5)
I hope this makes sense and someone is feeling charitable enough to help - I'm not an excel novice - but I really struggled to get my head round this - I think I'll need to Nest a Percentiles Function in a SUMIFS function - but I'm not sure how for this?
Thanks for reading, Ryan
Bookmarks