Conditional Formatting and other Dilemmas

buznac

New member
Joined
May 14, 2014
Messages
9
Reaction score
0
Points
0
Hi All,

I have the following dilemmas:
1. If cells "A3-A1000" value > cells "B3-B1000" value would like to display "Incline" in cells "C3-C1000" and opposite if < to show "Decline"?
2. One new tab added each corresponding month (Jan, Feb, Mar etc) with new data. First tab ("Annual") has a sum of all months data. I would like in tab "Annual" to have a new column that will highlight the increase or decrease from the previous month numbers with the above conditional formatting showing an arrow up/down or word "Incline"/"Decline" instead of value.
Thank You so much for input.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
In C3

=IF(A3>B3,"Incline",IF(A3<B3,"Decline","Equal"))

and copy down
 

buznac

New member
Joined
May 14, 2014
Messages
9
Reaction score
0
Points
0
Thank You so much Bob. One more question. How can I have on the "Annual" sheet this formula adapting automatically so each month will compare "Annual" with last month, basically adapting your formula so "A3" is last month (each month data are on different sheet) with "B3" (the Annual total to date - Sum of 12 months). Basically how can I have A3 jumping each month to "last month". Is there any formula that can be adapted to this. Thanks so much for any input.
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
=IF(INDIRECT(TEXT(EOMONTH(TODAY(),-1),"mmmm")&"!A3")>B3,"Incline",IF(INDIRECT(TEXT(EOMONTH(TODAY(),-1),"mmmm")&"!A3")<B3,"Decline","Equal"))
 

buznac

New member
Joined
May 14, 2014
Messages
9
Reaction score
0
Points
0
Thanks for your help. Did I told you I am kind of newbie :) Anyway do I have to change something in the formula (mmmm) or anything else as it is not working with copy/paste. Thanks.
 

buznac

New member
Joined
May 14, 2014
Messages
9
Reaction score
0
Points
0
Update

=IF(INDIRECT(TEXT(EOMONTH(TODAY(),-1),"mmmm")&"!A3")>B3,"Incline",IF(INDIRECT(TEXT(EOMONTH(TODAY(),-1),"mmmm")&"!A3")<B3,"Decline","Equal"))

Hi Bob. I attached the spreadsheet with a little explanation on the annual tab. Thanks so much
 

Attachments

  • Gold Members.xlsm
    16.1 KB · Views: 8

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Surely, the sum of all months will always be greater than the current month value, so it will always be Incline won't it?
 
Top