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
i understnad
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.
