Excel - IF Function Problem - Expert Solution

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

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.