Question description:
This user has given permission to use the problem statement for this
blog.
conditional format required for cell if another cell is 100% and cell is less than 100%
Solved by A. S. in 11 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
16/07/2018 - 01:10
Hi i need a conditional format to turn cells B red if they are less than A when 100% and they are less then 100% themselves
Excelchat Expert
16/07/2018 - 01:10
Hello, I understand that you need help with conditional formatting.
User
16/07/2018 - 01:11
If A is 100% turn B red unless B is 100% then turn green
User
16/07/2018 - 01:11
if(and?
Excelchat Expert
16/07/2018 - 01:11
I see. Okay, 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
16/07/2018 - 01:12
Will you be able to provide an example using the document to the right?
Excelchat Expert
16/07/2018 - 01:12
I mean, if you could provide all possible situations, it would help me a lot.
Excelchat Expert
16/07/2018 - 01:12
How about the rest?
Excelchat Expert
16/07/2018 - 01:13
So A doesn't have to be 100%?
User
16/07/2018 - 01:13
I already have a conditional format for that but i am struggling on this one
User
16/07/2018 - 01:13
When A is 100%
Excelchat Expert
16/07/2018 - 01:14
Don't worry, I'm here to help. I'm just trying to fully understand what you need and what you already have implemented so I can provide an accurate solution.
Excelchat Expert
16/07/2018 - 01:14
So, let's start from scratch, give me the exact rule you need even though you already have the rule for when A is 100% so I can create one that works for you.
Excelchat Expert
16/07/2018 - 01:16
I see. Thank you. That's very helpful.
Excelchat Expert
16/07/2018 - 01:16
So these 4 are all the rules then?
User
16/07/2018 - 01:17
I have 3 rules i cannot get the 4th in cell 14 to work
User
16/07/2018 - 01:17
a14
Excelchat Expert
16/07/2018 - 01:17
Oh, so first 3 are all set, okay. One moment.
Excelchat Expert
16/07/2018 - 01:18
Based on how you worded your 4th rule, this should work:
Excelchat Expert
16/07/2018 - 01:18
=and(A2=100,B2<100)
User
16/07/2018 - 01:19
So i will have red amber green and also another red conditional format. 4 formats altogether right?
Excelchat Expert
16/07/2018 - 01:19
It depends on how you setup your priorities.
User
16/07/2018 - 01:20
I can move them up and down untill correct
Excelchat Expert
16/07/2018 - 01:20
Yep, that's all you need to do.
User
16/07/2018 - 01:20
Thank you so much
Excelchat Expert
16/07/2018 - 01:20
=and(A2=100,B2<100) simply checks A if it is 100, then checks B if it is less than 100.
User
16/07/2018 - 01:20
Do i close the page
Excelchat Expert
16/07/2018 - 01:20
Alright, if you have no other questions regarding the original concern, I'd appreciate a 5-star rating and your feedback if you think I deserve it.
Excelchat Expert
16/07/2018 - 01:20
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
16/07/2018 - 01:21
Please do not forget to click the End Session button otherwise I'll be stuck here until the timer expires and be unable to help others. Thank you.
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.
