Question description:
This user has given permission to use the problem statement for this
blog.
I need a formula for conditional formatting a cell based on a percentage of another cell, This is for a project Timeline Manager chart so would like the formula to auto copy when adding new projects.
C1 = blank H3 = percentage data bar 0-100%
I would like:
C1 to input "Not Started" if Percentage of H3 is 0%
C1 to input "In Progress" if percentage of H3 is between 1-99%
C1 to input "complete" if percentage of H3 is 100%
Solved by I. J. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
20/07/2018 - 03:32
Welcome, thank you for choosing Got It Pro-Excel! I'll be glad to assist you with your concern. :)
User
20/07/2018 - 03:32
Great i would greatly appreciate it!
Excelchat Expert
20/07/2018 - 03:32
Okay, I've read your concern, and it seems you wish to automate the response of C1 based on the percentage of H3. Is this correct? :)
User
20/07/2018 - 03:33
I am still fairly new with excel, trying to learn just online but i can not seem to figure this one out.
User
20/07/2018 - 03:33
yes that is correct
Excelchat Expert
20/07/2018 - 03:33
Okay I see, I can help with your concern. The formula would be a nested IF statement. Let me provide you the solution to be entered in cell C1 then we can discuss how this formula works.
User
20/07/2018 - 03:34
ok great sounds good thank you!
Excelchat Expert
20/07/2018 - 03:36
Okay, here's the formula you need to enter in cell C1:
Excelchat Expert
20/07/2018 - 03:36
=IF(H3=0,"Not Started",IF(AND(H3>0,H3<1),"In Progress",IF(H3=1,"Completed","")))
User
20/07/2018 - 03:37
ohhh ok that makes sense!! i would put that right on the spreadsheet i wouldn't have to make that a conditional format rule right?
Excelchat Expert
20/07/2018 - 03:38
That is correct! Conditional formatting is only used to edit the "format" of cells (like highlight, text color, bold, italics, etc.) when a certain condition is met.
Excelchat Expert
20/07/2018 - 03:38
In our case, we don't need to use this function of Excel.
User
20/07/2018 - 03:38
And would i have to anchor any of the columns or rows in that formula or will it automatically update itself when i add new projects onto the spreadsheet?
Excelchat Expert
20/07/2018 - 03:39
We just need to know the value of H3 and return the corresponding response in C1.
User
20/07/2018 - 03:39
Ahhhh ok i got it now. that makes sense!
Excelchat Expert
20/07/2018 - 03:39
By adding new projects onto the spreadsheet, what do you mean by this? :)
User
20/07/2018 - 03:40
So on H3 i have a "Data Bar" conditional formatting rule. this will not affect how C1 when looking for the value right?
Excelchat Expert
20/07/2018 - 03:40
That is correct. C1 only reads the value, not the format, of H3.
Excelchat Expert
20/07/2018 - 03:42
If for example you're going to add another data bar on cell H4, and you want to link the response of this in cell C2, you can just drag down the formula from cell C1 to C2 and the references in the formula will automatically adjust.
Excelchat Expert
20/07/2018 - 03:43
Do you have any more concerns regarding the provided solution? :)
User
20/07/2018 - 03:43
I mean if i need to add more rows to add another string of data, i would normally just click and drag the little square at the bottom of C1 and copy the formula down. I am to understand when doing certain formulas i need to add an "Anchor" or "$" before the letter or number.. Example $C$1.. i am not to sure exactly what that means, but for this formula would i need the anchors or will it copy and paste just fine the way we have it written here?
Excelchat Expert
20/07/2018 - 03:44
Oh I see what you mean.
Excelchat Expert
20/07/2018 - 03:45
In the formula provided, when you drag it down to C2, the reference cells will as well be dragged down. So in C1, we have it referenced to H3. In C2, we will have it referenced to H4.
Excelchat Expert
20/07/2018 - 03:45
The role of the anchors ($) is to make a reference as absolute.
User
20/07/2018 - 03:45
ohh ok perfect! :)
Excelchat Expert
20/07/2018 - 03:46
For example, if you have the formula =IF($H$3=0,"Not Started",IF(AND($H$3>0,$H$3<1),"In Progress",IF($H$3=1,"Completed","")))
Excelchat Expert
20/07/2018 - 03:46
instead of the one that was earlier provided, the H3 reference will not change even if you drag it down, up or across.
Excelchat Expert
20/07/2018 - 03:47
So the anchors will make a reference to be "absolute", meaning they will not be changed when the formula is copied elsewhere.
User
20/07/2018 - 03:47
ok so does the $H mean the Column is anchored and $3 means the Row is anchored?
Excelchat Expert
20/07/2018 - 03:47
That is correct!
Excelchat Expert
20/07/2018 - 03:48
So basically, if you want to make it anchored in column H but not on row 3, use $H3. If you want to anchor it on row 3 but on on column H, use H$3.
Excelchat Expert
20/07/2018 - 03:48
If you want to anchor it on both column H and row 3, use $H$3.
User
20/07/2018 - 03:49
ok cool. so i have made a gantt chart using conditional formatting on this project as well and the formulas tell me to use the anchors $M8:$BN75 for my chart area. So this means it will copy down more rows if i need to extend the area but it will not extend to another column?
Excelchat Expert
20/07/2018 - 03:50
That is correct! When you copy it down by one row, it will become $M9:$BN76. However, if you copy it across, it will still remain as $M8:$BN75.
User
20/07/2018 - 03:51
ok awesome thank you so much for your help! this site here is amazing i have been searching for the answer to this problem for like 2 days and you had it to me and more answers in minutes!
Excelchat Expert
20/07/2018 - 03:51
If you don't have any more clarifications at this point, you may end the session by clicking the END SESSION on the upper right of your screen.
Excelchat Expert
20/07/2018 - 03:51
Also, I'd appreciate if you could drop a few lines for your kind and honest feedback on a survey after this session. Many thanks and have a good day!
Excelchat Expert
20/07/2018 - 03:51
Glad to be of help! :)
User
20/07/2018 - 03:52
Thank you so much! Enjoy the rest of your day!!
Excelchat Expert
20/07/2018 - 03:52
You as well! :)
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.
