All solutions IF Expert Solution – Excel IF Problems

Excel - IF Function Problem - Expert Solution

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

i am trying to make a box change colors based on another boxes value. the issue is I need it to change based on multiple boxes depending on which one is most recent. example, box c4 is white unless box f4 is red, then it turns red as well, however if box g4 is green then c4 is no longer red but instead turns back to white.
Solved by C. Q. in 51 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 05/10/2018 - 10:44
Welcome to ExcelChat!
Excelchat Expert 05/10/2018 - 10:44
I see that your question is about conditional formatting. I will help you with the problem and explain the solution afterwards.
Excelchat Expert 05/10/2018 - 10:44
Before we get started, this is a reminder that our policy is one problem per session with additional Q&A on that problem as time allows.
Excelchat Expert 05/10/2018 - 10:44
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert 05/10/2018 - 10:46
I see that you want to base the conditional formatting based on the fill of another cells
Excelchat Expert 05/10/2018 - 10:46
It is not possible to do, but we can make a nice workaround
Excelchat Expert 05/10/2018 - 10:46
We can make, for example, cell A1 green, if it has text green inside (we can make the text invisible)
Excelchat Expert 05/10/2018 - 10:46
And then we can make cell A2 red, if cell A1 green (it will be based on the invisible text in A1)
Excelchat Expert 05/10/2018 - 10:47
So it will work as you want, the only thing that the manual fill which you do for some of the cells will be done by entering a specific text to those cells
Excelchat Expert 05/10/2018 - 10:47
If you like this solution, please share all the rules that we should apply to it
User 05/10/2018 - 10:49
I attached a file of what im working on, hopefully it helps to explain
[Uploaded an Excel file]
User 05/10/2018 - 10:50
i am attempting to track progressing of training, with the operators name turning red if they aren't up to the expected speed for that week.
Excelchat Expert 05/10/2018 - 10:50
Okay, let's discuss the rules
Excelchat Expert 05/10/2018 - 10:51
I see that for week1 they are the following:
User 05/10/2018 - 10:51
if the following week they achieve the expected speed then i would like that info to supercede the previous data
Excelchat Expert 05/10/2018 - 10:51
As I understand, the cell F2:F6 should be red if their value is 0-10, yellow if 11-20, and green if 21-100, right?
User 05/10/2018 - 10:52
yes correct
Excelchat Expert 05/10/2018 - 10:52
You also have similar rules for week2 and week3
Excelchat Expert 05/10/2018 - 10:52
What about weeks 4-6?
User 05/10/2018 - 10:53
i have the formulas in those boxes as 0-84 = red 85-100=green
Excelchat Expert 05/10/2018 - 10:54
Okay, so if we sum up, here is what you are looking for:
Excelchat Expert 05/10/2018 - 10:54
F2:F6: 0-10 red, 11-20 yellow, 21-100 green
Excelchat Expert 05/10/2018 - 10:54
G2:G6: 0-30 red, 31-40 yellow, 41-100 green
Excelchat Expert 05/10/2018 - 10:54
H2:H6 0-60 red, 61-84 yellow,85-100 green
Excelchat Expert 05/10/2018 - 10:55
I2:K6: 0-84 red, 85-100 green
Excelchat Expert 05/10/2018 - 10:55
Is it all the conditional formattings which I have to make? Or do you have some other rules?
User 05/10/2018 - 10:57
yes, i have all of those formulas applied currently. what i would like is for the name in column C to turn red if one of the boxes in rows F,G,H,I,J,K are red. but turn back to white if the following week is no longer red.
Excelchat Expert 05/10/2018 - 10:58
You have merged cells in column C. Do you mean that you need this cell to be red if any of the cells from range F2:K6 is red?
User 05/10/2018 - 10:58
ex. if f5 is red and no info is present for weeks 2,3,4,5,6 then name = red. however when data is entered into g5 that shows no longer red then i would like the name to no longer be red
Excelchat Expert 05/10/2018 - 10:59
Oh, I got your problem. So if the latest week is not red, then cell is not red as well. If the latest is red, then it is red as well, right?
User 05/10/2018 - 10:59
yes correct
Excelchat Expert 05/10/2018 - 11:00
Great, I will work on the solution for about 10-15 minutes. Please extend the session
User 05/10/2018 - 11:00
how do i extend?
Excelchat Expert 05/10/2018 - 11:00
In general I will just implement all those rules and add some logic..
Excelchat Expert 05/10/2018 - 11:00
You should see the pop up
Excelchat Expert 05/10/2018 - 11:00
Thank you
User 05/10/2018 - 11:00
Thank you
Excelchat Expert 05/10/2018 - 11:03
Will you fill the data for each row together?
Excelchat Expert 05/10/2018 - 11:03
Like when you enter week2, will it be filled for rows 2:6?
User 05/10/2018 - 11:04
yes week twos data for 2-6 will all be manually entered at the same time but will not all be the same value
Excelchat Expert 05/10/2018 - 11:04
Okay, thank you
Excelchat Expert 05/10/2018 - 11:04
Will you fill each week one after another without skipping?
User 05/10/2018 - 11:05
yes there would not be info for week 3 without info for week 2 being entered first
Excelchat Expert 05/10/2018 - 11:11
Sorry, some technical issue
User 05/10/2018 - 11:13
is there anything i should be doing while im waiting?
Excelchat Expert 05/10/2018 - 11:14
I work on the problem, but ExcelChat disconnects me all the time after few seconds of reloading the page. If I won't be able to write you anything till the end of the session, please contact the support by email, they will send you my solution
Excelchat Expert 05/10/2018 - 11:14
Oh, it stopped doing it
Excelchat Expert 05/10/2018 - 11:14
Great
Excelchat Expert 05/10/2018 - 11:15
No, I will write you once I finish
Excelchat Expert 05/10/2018 - 11:19
When you see the pop up, please extend the session again. Sorry, the rule is pretty long.
User 05/10/2018 - 11:19
ok
Excelchat Expert 05/10/2018 - 11:24
Your result is ready
[Uploaded an Excel file]
Excelchat Expert 05/10/2018 - 11:24
Please check it
Excelchat Expert 05/10/2018 - 11:25
Keep in mind the rules which we discussed
Excelchat Expert 05/10/2018 - 11:26
The formula which I used for this rule
Excelchat Expert 05/10/2018 - 11:26
=OR( AND(MIN(K2:K6)<85,COUNTBLANK(K2:K6)=0), AND(MIN(J2:J6)<85,COUNTBLANK(J2:J6)=0,COUNTBLANK(K2:K6)=ROWS(K2:K6)), AND(MIN(I2:I6)<85,COUNTBLANK(I2:I6)=0,COUNTBLANK(J2:K6)=ROWS(K2:K6)*2), AND(MIN(H2:H6)<61,COUNTBLANK(H2:H6)=0,COUNTBLANK(I2:K6)=ROWS(K2:K6)*3), AND(MIN(G2:G6)<31,COUNTBLANK(G2:G6)=0,COUNTBLANK(H2:K6)=ROWS(K2:K6)*4), AND(MIN(F2:F6)<11,COUNTBLANK(F2:F6)=0,COUNTBLANK(G2:K6)=ROWS(K2:K6)*5), )
Excelchat Expert 05/10/2018 - 11:26
This formula is well formatted at cell D10
User 05/10/2018 - 11:27
it is a protected sheet i cant see if it works properly
Excelchat Expert 05/10/2018 - 11:27
Hm.. it shouldn't be protected
Excelchat Expert 05/10/2018 - 11:28
Please try to download it again
Excelchat Expert 05/10/2018 - 11:28
[Uploaded an Excel file]
User 05/10/2018 - 11:28
it works perfectly thank you so much
Excelchat Expert 05/10/2018 - 11:28
Great to hear that!
User 05/10/2018 - 11:29
my company computer would not allow me to access it at first
Excelchat Expert 05/10/2018 - 11:29
Do you have questions about the solution?
Excelchat Expert 05/10/2018 - 11:29
Please study this formula at cell D10, I tried to format it in understandable way
Excelchat Expert 05/10/2018 - 11:29
Basically we check each week step by step starting from the end
Excelchat Expert 05/10/2018 - 11:29
First we check that it is not blank and that the minimum value fits the criteria
Excelchat Expert 05/10/2018 - 11:30
If something breaks the set limit, it means that formula will return True and cell will become red
Excelchat Expert 05/10/2018 - 11:30
Then we go to the next week
Excelchat Expert 05/10/2018 - 11:30
We check that week6 is empty, and week5 is not empty, and that it follows the rules
Excelchat Expert 05/10/2018 - 11:30
And so on we move to the final column
Excelchat Expert 05/10/2018 - 11:31
In final column we check if weeks 2:6 are empty, if week1 is note empty, and if it follows the set limit
Excelchat Expert 05/10/2018 - 11:31
The logic is the same, we just produce it step by step
Excelchat Expert 05/10/2018 - 11:33
If you don't have more questions, I believe we may finish. It was a pleasure to assist you! If it happens that you have any Excel or Google Sheets problems in future, please don't hesitate to reach us again.
Excelchat Expert 05/10/2018 - 11:33
You may now end the session, I would highly appreciate if you rate our session at the end and leave some feedback. Have a nice day!
User 05/10/2018 - 11:35
it will take me a while to understand this formula, it is far more complex than anything i have worked on before i appreciate your help and will gladly give feedback. good day
Excelchat Expert 05/10/2018 - 11:35
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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc