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

Interesting Problem

Montrey

Montrey

Member
I have a dynamic set of Op income/total expense data.

For the revenue departments it is Op income.

For the Expense departments it is total expense.

I want to use conditional formatting to show green as good and red as bad.

Green would be +5% over last year for the op income.

Red would be - 5% over last year for the op income.


For expense though it is the reverse.

Green would be -5% over last year.

Red would be +5% over last year.


Can you guys help me out with this? Or give me another idea of how to display this.

The idea is that a person should just be able to look for the Red cells and then question managers of the respective department of why their op income/expense is bad.


Thank you!! I've been stuck on this forever! It is the last thing I need before my dashboard is done!
 
Hi, Montrey!


Suppose your data is from A1 like this And you want to CF B3:B5 and D3:D5 ranges:

-----

[pre]
Code: 
Op income	Revenue		Total		Expense
Last year	This year	Last year	This year
100		90		100		90
100		100		100		100
100		110		100		110
[/pre]
-----


Try applying this two rules to both ranges:

green: =O(Y(B$1="Revenue";B3>A3*1,05);Y(B$1="Expense";B3<A3*0,95)) -----> in english: =OR(AND(B$1="Revenue",B3>A3*1.05),AND(B$1="Expense",B3<A3*0.95))

red: =O(Y(B$1="Revenue";B3<A3*0,95);Y(B$1="Expense";B3>A3*1,05)) -----> in english: =OR(AND(B$1="Revenue",B3<A3*0.95),AND(B$1="Expense",B3>A3*1.05))


I don't know if this is what you've asked for. If it isn't, please consider uploading a sample file with manually fixed examples. Thanks.


Regards!
 
It looks like I am going to have to have a very complicated Conditional Formatting equation. Because the expenses and the op. income are in the same column and it is dynamic.


I am going to have to make 2 equations.

Green would be this: that if the name of the department matches a list of expense departments and if offset(ty expense)-offset(ly expense)/offset(ly expense)<.05,true,if(and(match(revenue name list),offset(ty opincome)-offset(ly opincome)/offset(ly opincome)>.05),true,false)

The above equation formats the cell green if the number is matched with an expense and the difference between the two years is less than 5% or if the number is matched with revenue and the difference between this year and last year is greater than 5%.


The red formatting equation would be the opposite with respect to the < or > symbols.

Now time to write this beast of an equations. I'll post it when i'm done I guess.
 
Hi, Montrey!

Maybe you can upload a sample file with instructions/examples? That would help a lot.

Regards!
 
Hey I figured it out. Here is the result for the conditional formats! It took 5 hours but i finally got it. White means YoY is flat, Green means good, Red means bad. Check out the formulas!


For White:

=IF(AND(($DG31-$DH31)/$DH31<0.05,($DG31-$DH31)/$DH31>-0.05),1,0)


For green:

=IF(OR(AND(ISNUMBER(MATCH($DF31,costdepartments,0)),($DG31-$DH31)/$DH31<0.05),AND(ISNUMBER(MATCH($DF31,revdepartments,0)),($DG31-$DH31)>0.05)),1,0)


For Red:

=IF(OR(AND(ISNUMBER(MATCH($DF31,costdepartments,0)),($DG31-$DH31)/$DH31>0.05),AND(ISNUMBER(MATCH($DF31,revdepartments,0)),($DG31-$DH31)<0.05)),1,0)
 
Hi, Montrey!

Glad to hear you solved it. Good job. Welcome back whenever needed/wanted.

Regards!
 
Sorry to butt in here but I have been trying to post question on this forum for 2 days. My posts never appear in the "posted by" column, and I've posted 3 messages. I thought maybe someone could tell me why that is?


I'm logged in; from California.
 
You must log in or register to reply here.
Back
Top