• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF statement with conditional formatting

3G

3G

Member
Hello-

I'm trying to use Icon Sets to display % actual complete vs expected % complete for a software build.
The % complete is purely manually entered, however, the expected % complete is calculated based on a formula where the Elapsed Days of Build is Divided by the Total Days of Build. For example, 100 days for total days of build, 50 days have elapsed, the Expected % complete is 50%.

What I'm trying to show is that if the % actual complete is falling behind the expected, then I can show both where it SHOULD be (a green icon), and where it actually IS (yellow or red icon).

If they fall in between the same % brackets, then, there should only be 1 icon of "Green" indicating the project is essentially on track.

I've attached a file for sample...

Thanks!
 

Attachments

  • Chandoo_HelpFile.xlsx
    27.2 KB · Views: 7
Hi, 3G!
A few questions about your uploaded file. Let me take the S:W columns from rows 9:11, which seem to use column F from 2nd worksheet.
a) Row 9 in 1st worksheet is tied to row 3 in 2nd one, and row 11 in 1st to row 4 in 2nd? If so, what about row 10?
b) You have 5 values, 0%-25%-50%-75%-100%, to which of them should these percentages correspond? 37%, 37.5%, 38%
c) For cell V9, the comment says you want a "Green Dot" but that there's a yellow one in other range (cell T9). Would you please elaborate how and how many dots are to be assigned?
Regards!
 
a). Yes SirJB97. That is my fault. I thought I'd removed/unhidden that line. Please assume that it should be Row 10.
b). 37% will correspond to "25%". The goal is that UNTIL That value is equal to, or, exceeds the next heading (in the case of 37%, that would be 50%), it stays in the 25%. So the values would look something like this:
0=0-24.%
25=25-49.9%
50=50-74.9%
75= 75-99.9%
100 = 100%
c). Let's use your value of 37% as an example. This value would fall under the "25%" heading. Now, if the % Expected Complete is 75%, there should be 2 dots. One yellow (for the 37%), and one Green (for the 75%). Again, this is just showing where something IS currently (the 37%), and where it should be (75%). If BOTH were 37%, or, say, fell with in the ranges above (i.e. one is 25%, one is 37%), then there'd be ONE green dot since they're in the same range (of 25-49.9%...one 25% & one 37%).

Does that make sense?
 
Hi, 3G!
And what if actual exceeds expected? In ranges I mean, expected 40% (25% bucket) and actual 55% (50% bucket).
Regards!
 
SirJB7...They'd flip..The Actual would be "ahead" of the "Expected". You're example is correct that the Actual would be in the 50%, and, the expected in the 25%. The colors are kind of irrelevant, as eventually they'll probably become webdings (X's, circles...whatever) or something else shape-wise. For now, I'm just trying to get the right behavior to happen.
 
Good day 3G


This forum operates 24/7 with members in countries all around the world, a question may be answered by someone and then go quite, there are many reasons for this as members have many things to do outside of the Forum, eat, sleep, work, hobbies, family the list goes on and on. A reply and answer may be urgent and important to you but not to the members.
SirJB7 answered you on Friday, today being Monday he may have been away during the weekend in the Sierras de Córdoba’s mountain range giving his offerings to the gods of Carlsberg, be patient, if he has an answer he will respond.
Get yourself some coffee and some Moravian Sugar Cake... chill out
 
@3G
Hi!
What about this?
http://chandoo.org/forum/threads/dr...-formula-based-on-selection.12719/#post-74889
... with the proper fix:
http://chandoo.org/forum/threads/dr...-formula-based-on-selection.12719/#post-74904
... while I try to reset myself to the non-weekend status and give a look again to this issue.
Regards!

@b(ut)ob(ut)hc
Hi, buddy!
Not Cordoba this time, just Tandil, 350 Km instead of 800.
Regards!
PS: Last weekend at Cordoba, Villa General Belgrano, a traditional German colony, the annual Oktoberfest had taken place, but I have a family birthday on 11th so I can never attend:
http://gosouthamerica.about.com/od/argchaco/p/Oktoberfest.htm
 
Hi, 3G!

Give a look at the uploaded file. Let us take the Build range as an example. I deleted the ghost row and added a new one to simulate the remaining case:
1st, completed < budgeted
2nd, completed = budgeted
3rd, completed > budgeted

I chose an icon set of 4 elements consisting on:
white dot, 0
yellow/orange dot, 1
green dot, 2
green dot on black square, 3

And the formula for the completion / budget value is this:
=SI(SI.ERROR(COINCIDIR(MstrCdLst!$F3;$S$8:$W$8;1);0)=COINCIDIR(S$8;$S$8:$W$8;0);SI(SI.ERROR(COINCIDIR(MstrCdLst!$F3;$S$8:$W$8;1);0)<SI.ERROR(COINCIDIR(MstrCdLst!$M3;$S$8:$W$8;1);0);1;SI(SI.ERROR(COINCIDIR(MstrCdLst!$F3;$S$8:$W$8;1);0)>SI.ERROR(COINCIDIR(MstrCdLst!$M3;$S$8:$W$8;1);0);3;2));0)+SI(SI.ERROR(COINCIDIR(MstrCdLst!$M3;$S$8:$W$8;1);0)=COINCIDIR(S$8;$S$8:$W$8;0);SI(SI.ERROR(COINCIDIR(MstrCdLst!$F3;$S$8:$W$8;1);0)=SI.ERROR(COINCIDIR(MstrCdLst!$M3;$S$8:$W$8;1);0);0;2)) -----> in english: =IF(IFERROR(MATCH(MstrCdLst!$F3,$S$8:$W$8,1),0)=MATCH(S$8,$S$8:$W$8,0),IF(IFERROR(MATCH(MstrCdLst!$F3,$S$8:$W$8,1),0)<IFERROR(MATCH(MstrCdLst!$M3,$S$8:$W$8,1),0),1,IF(IFERROR(MATCH(MstrCdLst!$F3,$S$8:$W$8,1),0)>IFERROR(MATCH(MstrCdLst!$M3,$S$8:$W$8,1),0),3,2)),0)+IF(IFERROR(MATCH(MstrCdLst!$M3,$S$8:$W$8,1),0)=MATCH(S$8,$S$8:$W$8,0),IF(IFERROR(MATCH(MstrCdLst!$F3,$S$8:$W$8,1),0)=IFERROR(MATCH(MstrCdLst!$M3,$S$8:$W$8,1),0),0,2))

Which might be traduced to:
= CompletionPart + BudgetPart
CompletionPart: IF(IFERROR(MATCH(MstrCdLst!$F3,$S$8:$W$8,1),0)=MATCH(S$8,$S$8:$W$8,0),IF(IFERROR(MATCH(MstrCdLst!$F3,$S$8:$W$8,1),0)<IFERROR(MATCH(MstrCdLst!$M3,$S$8:$W$8,1),0),1,IF(IFERROR(MATCH(MstrCdLst!$F3,$S$8:$W$8,1),0)>IFERROR(MATCH(MstrCdLst!$M3,$S$8:$W$8,1),0),3,2)),0)
BudgetPart: IF(IFERROR(MATCH(MstrCdLst!$M3,$S$8:$W$8,1),0)=MATCH(S$8,$S$8:$W$8,0),IF(IFERROR(MATCH(MstrCdLst!$F3,$S$8:$W$8,1),0)=IFERROR(MATCH(MstrCdLst!$M3,$S$8:$W$8,1),0),0,2))

CompletionPart:
If completion rank = column rank Then (If completion rank < budget rank Then 1 Else If completion rank > budget rank then 3 Else 2) Else 0
BudgetPart:
If completion rank = column rank Then (If completion rank = budget rank Then 0 Else 2)

So for the 3 examples (completion vs. budget):
25 vs 50
50 vs 50
75 vs 50
the terms will be as (for each bucket/column)
0+0, 1+0, 0+2, 0+0, 0+0
0+0, 0+0, 2+0, 0+0, 0+0
0+0, 0+0, 2+0, 3+0, 0+0
corresponding the 1st figure to the completion component and the 2nd to the budget component.

Just advise if any issue.

Regards!

PS: I also changed the format from No. to Percentage and divided values by 100, in column M, so as to check them properly against column F. If for any reason you might keep the original format and values, take care to adjust the formulas referencing to M column dividing them by 100 (in the posted formula).
 

Attachments

  • IF statement with conditional formatting - Chandoo_HelpFile (for 3G at chandoo.org).xlsx
    23.6 KB · Views: 4
WOW. JUST, WOW.

Thank you very very much! I sincerely appreciate it!!!

I will mess around with it, and post back if there are any issues.
 
SirJB7- Is there a way to add another icon/argument when the two "overlap" (not necessarily equal, but, in the same range...26% & 37%)? Say, a green circle in a black square?
 
(sorry, for some reason, I cannot edit my previous post, so this is a new line) or if they WERE, say, 100% & 100%?
 
@3G

There is a time limit on post editing, this stops members removing post and denying others the opportunity to see questions and answers.
 
3G said:
WOW. JUST, WOW.

Thank you very very much! I sincerely appreciate it!!!

I will mess around with it, and post back if there are any issues.
Click to expand...
Hi, 3G!
Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.
Regards!
PS: I had just loaded and aimed the almost automatic auto-response... but it'll have to wait... but what to do with the clipboard content?... Better use it :)
3G said:
SirJB7- Is there a way to add another icon/argument when the two "overlap" (not necessarily equal, but, in the same range...26% & 37%)? Say, a green circle in a black square?
Click to expand...

Hi, 3G!
CF conditions with icon sets have at most 5 icons, so as we're using 4, you actually have 1 more available. But if I don't read you wrongly, you actually don't need that new condition, since the available cases are (speaking of ranks, not values):
yellow/green, completed < budgeted (2 dots)
green, completed = budgeted (1 dot)
green/yellow-squared, completed > budgeted (2 dots)
If you want an extra condition, it'd be replacing the green dot, so you'll be still having 3 conditions (+1 for the zero value).
Am I wrong?
Regards!

SirJB7 said:
@3G
Hi!
What about this?
http://chandoo.org/forum/threads/dr...-formula-based-on-selection.12719/#post-74889
... with the proper fix:
http://chandoo.org/forum/threads/dr...-formula-based-on-selection.12719/#post-74904
... while I try to reset myself to the non-weekend status and give a look again to this issue.
Regards!
Click to expand...
Hi, 3G!
Since you've kindly and gently offered this:
3G said:
Hi SirJB7 - do you need anything else from me, my good man?
Click to expand...
... I think you're still missing something, maybe regarding the previous quote? ;)
Regards!
 
Ok. I see what you're saying. I can just keep it the way it is, as after looking at it a few times, it's fine as-is.

Other changes I can make manually if necessary.

Again, much appreciated!
 
You must log in or register to reply here.
Back
Top