**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.*