# Excel - AVERAGE Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

I need to highlight all cells in red which are 75% above average
Solved by A. W. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 20/06/2018 - 12:36
Hello, I understand that you would like to highlight all cells that are 75% above average, right?
User 20/06/2018 - 12:37
hi can you see my excel sheet to the right
User 20/06/2018 - 12:37
?
Excelchat Expert 20/06/2018 - 12:38
Yes, I can.,
User 20/06/2018 - 12:38
ok so what i need to do is
User 20/06/2018 - 12:38
highlight all cells in red that are 75% higher than the average
User 20/06/2018 - 12:38
but i want it to use whatever percentage is in E10
Excelchat Expert 20/06/2018 - 12:39
I see. We can help you with this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
Excelchat Expert 20/06/2018 - 12:39
So in your example, what is 75% higher than the average?
User 20/06/2018 - 12:39
yes
Excelchat Expert 20/06/2018 - 12:39
I mean, what is 75% higher than the average? How do you compute for that?
Excelchat Expert 20/06/2018 - 12:40
616.2615 is 75% of 821.682
Excelchat Expert 20/06/2018 - 12:40
So we add those 2 and whichever is higher than that will get highlighted?
Excelchat Expert 20/06/2018 - 12:41
Please use this to send your message as we are not allowed to communicate outside the platform.
Excelchat Expert 20/06/2018 - 12:41
But yes, I do get your point now.
User 20/06/2018 - 12:41
thanks
Excelchat Expert 20/06/2018 - 12:41
Let me create the conditional formatting formula.
User 20/06/2018 - 12:41
however i don't want it worked out like this
User 20/06/2018 - 12:41
if i amend the 75 to 60 i want it to auto change
Excelchat Expert 20/06/2018 - 12:42
Please provide an accurate representation of your data so I can provide an accurate solution.
Excelchat Expert 20/06/2018 - 12:42
Columns must be accurate, rows must be accurate and the format of what you will be typing in E10 must be accurate.
Excelchat Expert 20/06/2018 - 12:42
Remember that 75 is not the same as 75% so I'll need to know how your input looks like in order to provide an accurate solution.
User 20/06/2018 - 12:44
ok
Excelchat Expert 20/06/2018 - 12:45
So using the sample you created, I made the conditional formatting.
Excelchat Expert 20/06/2018 - 12:45
You can change the value of e10 and the cells will get highlighted accordingly.
Excelchat Expert 20/06/2018 - 12:45
Note that this formula only works for this specific sample formatting you provided.
Excelchat Expert 20/06/2018 - 12:46
This is the custom rule for A7 which can then be applied to the rest:
Excelchat Expert 20/06/2018 - 12:46
=A7>((AVERAGE(\$A7:\$E7))*\$E\$10)+(AVERAGE(\$A7:\$E7))
User 20/06/2018 - 12:48
why is not working for below average >?
Excelchat Expert 20/06/2018 - 12:48
You used the conditional formatting formula which is designed to return true or false.
User 20/06/2018 - 12:49
ohhhhhhhhhhhhh
User 20/06/2018 - 12:49
User 20/06/2018 - 12:49
that's brilliant thank you so much
Excelchat Expert 20/06/2018 - 12:50
You are welcome. Feel free to download the file to the right to use as a reference.
Excelchat Expert 20/06/2018 - 12:50
A7 will have the conditional formatting rule applied to it so you can play with it.
Excelchat Expert 20/06/2018 - 12:52
Would there be anything else that I can help you with regards to the original question?
Excelchat Expert 20/06/2018 - 12:55
Hi, I have not received a reply from you for quite some time. I'm afraid the session will end automatically soon. I sincerely hope we were able to address your concern.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.