Formula strange statistics and formating

[Peltz]

Hello, I'm quite new to excel and im sorry if this is a really dumb question, but I can't really figure out a good solution. I would greatly apreciate some help formating some cells..

I have several ranges with numbers: (G7:G36;G38:G43;G49:G78;G80:G85;G91:G120;G122:G127)
Some of them will have numbers, others will be blank, but could have numbers in the future. Meaning that empty cells have to be ignored.

What I want to do is:
Have a quick way to identify very low or very high values in a list of numbers.

What I was thinking was:

In Cell H7
If G7 is in the upper 20% of the highest numbers in the range the number will turn red
If G7 is in the lower 20% of the lowes numbers in the range the number will turn green

This operation will be repeated for all the cells in collumn H, that is: (H7:H36;H38:H43;H49:H78;H80:H85;H91:H120;H122:H127)

By the way: I'm not excpecting that the values will follow a normal distibution.

Again, if someone could give me some help on how to express this excel, or if there is a better way of identifying outliers I would be very gratefull

Regards
The Excel Noob

 

[Expiry]

I presume the amount of figures you could have will change, so do you mean the top 20% of the numbers (ie. if there are 50 numbers, then it should be one of the highest 10 numbers) or do you mean within 20% of the highest number (ie. if the highest number is 100, then anything that is over 80 - even if that's every number)?

There are ways you can do either.

 

[Andrew Poulsom]

You could try these formulas:

=G7>PERCENTILE((G$7:G$36,G$38:G$43,G$49:G$78,G$80:G$85,G$91:G$120,G$122:G$127),0.8)

=G7<PERCENTILE((G$7:G$36,G$38:G$43,G$49:G$78,G$80:G$85,G$91:G$120,G$122:G$127),0.2)

Unfortunately you can't use them in conditional formatting because they include the union operator, but you could put them in a couple of spare columns and use the result of those columns for your format conditions.

 

[Peltz]

Thank you. The number i would like to highlight are the top 20% of the available numbers . That offcours implies that i have variation nd at least 10 different entries..

Im giving that formula a go