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

Conditional Formatting Question

J

jdkerr

New Member
I am trying to use Icon sets in conditional formatting for a dashboard application. I am tracking actual sales versus forecasted sales by calculating the forecast accuracy. For example if the forecast was 100 and actual sales were 120 the forecast accuracy would be 120/100 or 120%. I want to display a green icon if the forecast accuracy is between 90 - 100%, a yellow icon if the accuracy is between 80% - 120%. I haven't been able to figure out the right formula to use to do that. Can anyone help? Thanks
 
I'm not sure if you'd need a formula exactly.... select the cell with forecast accuracy value and go to Data Validation --> New Rule --> Format all cells based on value --> Select Icon Sets from Drop down --> and set your values/percentage as described above.... that should work
 
Jason, I think the trick is that you have a range of values you're trying to get in, and the green light is offset in it's range.
 
we'll need a helper column, but it can be done. With forecast in A2, actual in B2, setup this formula in C2:

=IF(ABS(B2-95%*A2)<=5%*A2,3,IF(ABS(B2-A2)<=20%*A2,2,1))

With C2 selected, go to Home - Conditional Formatting - Icon Sets - More Rules.


Change both the "Type" dropdowns to say "Number". For the green light, put a value of 3, and for the yellow light put a value of 2.

Pick desired icon set, and check the box for "Show icon only"

Ok out.


You should now have the formatting you desire.


EDIT: If the Green light is realy 90-110, formula is:

=IF(ABS(B2-A2)<=10%*A2,3,IF(ABS(B2-A2)<=20%*A2,2,1))
 
Thanks for Jason and Luke for your responses. I will take a look at your suggestions today and see if I can make them work. My spreadsheet is set up a little differently. I have the Forecast value stacked on top of the actual value. It looks like:


Forecast 100 call this cell B1

Actual 120 call this cell B2

% Variance 120% call this cell B3 (dividing 120/100


I can substitute the correct cell values into your formulas and give them a try. I am a little confused about why I need to have the formula =IF(ABS(B2-95%*A2)<=5%*A2,3,IF(ABS(B2-A2)<=20%*A2,2,1))

in my cell B3? It seems like we should be able to plug formulas into the conditional formatting icon screen directly to identify the correct icon to place in the cell. But I'll play around with it and see if I can make it work. Thanks for your help. I'll let you know how it works out.
 
Hi Luke. I set up a spreadhsheet using your formatting and it worked. Here's the issue I have. I want to be able to show the forecast variance as 120%. The formula you provided for C2 displays the number 2 which won't have meaning to my audience. Is there another way to skin this cat so I can show the forecast value in B2, the actual sales value in B3 and the variance of 120% in B3 AND pop up the correct icon? If you figure this one out I owe you lunch!
 
Not technically. You certainly can still have the variance in cell B3 and have the CF formula written nearby. If you still don't like the look, we could use the camera tool. This would mean putting the CF formula far away (hidden from normal use), and then using the camera tool to take a picture of the cell with icon and positioning it over the cell showing numerical variance. Plus side is that it might give you the appearance you want, downside is that it's harder to select the cell (since it's underneath a picture).


PS. The reason it won't work in the same cell is because you want the variance to check both ways, ie less than 90% or more than 110%. Icon CF rules MUST follow a "cell value is greater than..." type of format, and there's no way to do that with this situation. =/
 
I may have to rethink my scorecard and not use the icon feature but simply color in the cells. I've been able to use them for every other scenario but this one. Thanks for your input I really appreciate it.
 
Hi ,


Can you check this ? It does not use Icon sets , but is it acceptable ?


http://speedy.sh/3SdhQ/Conditional-Formatting.xlsx


Narayan
 
I will check this when I get home. We have this web filtering program here at work called Websense, which is nonsense, and it's blocking me from you link. GRRRRR
 
You must log in or register to reply here.
Back
Top