Excel Conditional Formatting

    Share

    wprager
    Administrator
    Administrator

    Number of posts : 47377
    Registration date : 2008-08-05

    Re: Excel Conditional Formatting

    Post by wprager on Fri Nov 29, 2013 4:35 pm

    tim1_2 wrote:Excel has a pretty good built-in tool for conditional formatting, but I don't know how to make the value in one cell affect the formatting in a different cell, if that's what you're going for.
    Under Conditional Formatting select New Rule and you get this interface:


    Click on the last option -- "Use a formula to determine which cells to format". Any cell references in the formula are relative to the cell where you were when you started (in my examples I assumed a heading row 1, then the first cell to format was in cell 2). When you copy the formatting to the cell below, that 2 will automatically change to a 3. The $ in front of the column marker is to make sure that the formula always checks the cell in that column. This way you can copy this formatting to a range of cells in the same row.

    shabbs
    Hall of Famer
    Hall of Famer

    Number of posts : 31416
    Registration date : 2008-08-12

    Re: Excel Conditional Formatting

    Post by shabbs on Fri Nov 29, 2013 5:40 pm

    wprager wrote:You cannot have a single rule with multiple outcomes.
    Sure you can. I do it all the time with statuses to show progress for tasks (Not Started, In Progress, Completed, Error). I have a single conditional statement that sets the colour depending the status. That is the whole point of conditional.

    TheAvatar
    Veteran
    Veteran

    Number of posts : 2728
    Registration date : 2008-08-05

    Re: Excel Conditional Formatting

    Post by TheAvatar on Sat Nov 30, 2013 9:30 am

    I'm the king of excel Smile
    avatar
    LeCaptain
    All-Star
    All-Star

    Number of posts : 12030
    Age : 29
    Location : Montreal, QC
    Favorite Team : Ottawa
    Registration date : 2008-12-01

    Re: Excel Conditional Formatting

    Post by LeCaptain on Sat Nov 30, 2013 12:59 pm

    Cmon Big Bad Ev, It's time to learn VBA you're gonna need it later if you want the best jobs Cool
    avatar
    shabbs
    Hall of Famer
    Hall of Famer

    Number of posts : 31416
    Location : I'm sorry, I can't hear you over the sound of how awesome I am.
    Favorite Team : Ottawa
    Registration date : 2008-08-12

    Re: Excel Conditional Formatting

    Post by shabbs on Sat Nov 30, 2013 3:53 pm

    LeKing wrote:Cmon Big Bad Ev, It's time to learn VBA you're gonna need it later if you want the best jobs Cool
    COBOL baby, COBOL.
    avatar
    wprager
    Administrator
    Administrator

    Number of posts : 47377
    Age : 55
    Location : Kanata
    Favorite Team : Ottawa
    Registration date : 2008-08-05

    Re: Excel Conditional Formatting

    Post by wprager on Sat Nov 30, 2013 10:13 pm

    shabbs wrote:
    wprager wrote:You cannot have a single rule with multiple outcomes.
    Sure you can. I do it all the time with statuses to show progress for tasks (Not Started, In Progress, Completed, Error). I have a single conditional statement that sets the colour depending the status. That is the whole point of conditional.
    I think we are talking about different things. You can only have *one* format setting for the cell background colour. So if yo need three colors you need three rules (unless you give the cells a "default" colour as the " else" ).

    If using VBA then, of course, you can have a single formula returning one of n values and then have the cell background set accordingly.


    _________________
    Hey, I don't have all the answers. In life, to be honest, I've failed as much as I have succeeded. But I love my wife. I love my life. And I wish you my kind of success.
    - Dicky Fox
    avatar
    wprager
    Administrator
    Administrator

    Number of posts : 47377
    Age : 55
    Location : Kanata
    Favorite Team : Ottawa
    Registration date : 2008-08-05

    Re: Excel Conditional Formatting

    Post by wprager on Sat Nov 30, 2013 10:16 pm

    shabbs wrote:
    LeKing wrote:Cmon Big Bad Ev, It's time to learn VBA you're gonna need it later if you want the best jobs Cool
    COBOL baby, COBOL.
    You mean use COBOL instead of BASIC in VBA? Like VCA? Puking2


    _________________
    Hey, I don't have all the answers. In life, to be honest, I've failed as much as I have succeeded. But I love my wife. I love my life. And I wish you my kind of success.
    - Dicky Fox
    avatar
    Ev
    Franchise Player
    Franchise Player

    Number of posts : 20610
    Age : 26
    Location : We The North
    Favorite Team : Ottawa
    Registration date : 2010-02-09

    Re: Excel Conditional Formatting

    Post by Ev on Mon Dec 02, 2013 10:20 am

    Thanks for the help, it worked.

    Now I have this formula and I'm lost lol

    For example,

    I have $100,000 on March 31, 2014. I am spending $10,000 per month. I want to know how many months I have until I run out of funds (from today's date).

    Any advice?
    avatar
    shabbs
    Hall of Famer
    Hall of Famer

    Number of posts : 31416
    Location : I'm sorry, I can't hear you over the sound of how awesome I am.
    Favorite Team : Ottawa
    Registration date : 2008-08-12

    Re: Excel Conditional Formatting

    Post by shabbs on Mon Dec 02, 2013 11:29 am

    This feels like we are doing your homework...

    Sarcasm
    avatar
    Ev
    Franchise Player
    Franchise Player

    Number of posts : 20610
    Age : 26
    Location : We The North
    Favorite Team : Ottawa
    Registration date : 2010-02-09

    Re: Excel Conditional Formatting

    Post by Ev on Mon Dec 02, 2013 11:32 am

    lol that's the last thing I need. I'm confused as hell

    We're a community here!!!
    avatar
    Ev
    Franchise Player
    Franchise Player

    Number of posts : 20610
    Age : 26
    Location : We The North
    Favorite Team : Ottawa
    Registration date : 2010-02-09

    Re: Excel Conditional Formatting

    Post by Ev on Mon Dec 02, 2013 12:06 pm

    OK, I have most of the formula. Just this one final part:

    This makes no sense to me. I subtract 03/31/2014 from today's date (to get the month numbers) and I get -9. WTF?

    I put in =MONTH(cell)-MONTH(NOW)...what am I doing wrong?

    I want the number to come up as 3 or 4 or whatever the number of months is until March 31.
    avatar
    Ev
    Franchise Player
    Franchise Player

    Number of posts : 20610
    Age : 26
    Location : We The North
    Favorite Team : Ottawa
    Registration date : 2010-02-09

    Re: Excel Conditional Formatting

    Post by Ev on Mon Dec 02, 2013 2:22 pm

    nevermind i got it now

    Still having issues with conditional formatting but I get the gist of it
    avatar
    wprager
    Administrator
    Administrator

    Number of posts : 47377
    Age : 55
    Location : Kanata
    Favorite Team : Ottawa
    Registration date : 2008-08-05

    Re: Excel Conditional Formatting

    Post by wprager on Mon Dec 02, 2013 2:32 pm

    Ev wrote:OK, I have most of the formula. Just this one final part:

    This makes no sense to me. I subtract 03/31/2014 from today's date (to get the month numbers) and I get -9. WTF?

    I put in =MONTH(cell)-MONTH(NOW)...what am I doing wrong?

    I want the number to come up as 3 or 4 or whatever the number of months is until March 31.
    So what did you do wrong, other than missing the "()" after "NOW")? One thing I would suggest is using today() instead of now() for dates.


    _________________
    Hey, I don't have all the answers. In life, to be honest, I've failed as much as I have succeeded. But I love my wife. I love my life. And I wish you my kind of success.
    - Dicky Fox
    avatar
    Ev
    Franchise Player
    Franchise Player

    Number of posts : 20610
    Age : 26
    Location : We The North
    Favorite Team : Ottawa
    Registration date : 2010-02-09

    Re: Excel Conditional Formatting

    Post by Ev on Mon Dec 02, 2013 2:34 pm

    I used another formula altogether:

    =(future date-TODAY()+1)/30)
    avatar
    wprager
    Administrator
    Administrator

    Number of posts : 47377
    Age : 55
    Location : Kanata
    Favorite Team : Ottawa
    Registration date : 2008-08-05

    Re: Excel Conditional Formatting

    Post by wprager on Mon Dec 02, 2013 2:46 pm

    Ev wrote:I used another formula altogether:

    =(future date-TODAY()+1)/30)
    Why? What you need is how many months have passed. You probably ignored the year.

    =((year(future)-year(today))*12)+(month(future)-month(today())))


    _________________
    Hey, I don't have all the answers. In life, to be honest, I've failed as much as I have succeeded. But I love my wife. I love my life. And I wish you my kind of success.
    - Dicky Fox
    avatar
    Ev
    Franchise Player
    Franchise Player

    Number of posts : 20610
    Age : 26
    Location : We The North
    Favorite Team : Ottawa
    Registration date : 2010-02-09

    Re: Excel Conditional Formatting

    Post by Ev on Mon Dec 02, 2013 3:08 pm

    No I don't need how many months have passed,

    The formula works, I just did it.

    You put in March 31, 2013 as future date, - today, and it gives you 4. That's correct.
    avatar
    Ev
    Franchise Player
    Franchise Player

    Number of posts : 20610
    Age : 26
    Location : We The North
    Favorite Team : Ottawa
    Registration date : 2010-02-09

    Re: Excel Conditional Formatting

    Post by Ev on Mon Dec 02, 2013 3:11 pm

    wait i migth be confused lol, just a sec
    avatar
    Ev
    Franchise Player
    Franchise Player

    Number of posts : 20610
    Age : 26
    Location : We The North
    Favorite Team : Ottawa
    Registration date : 2010-02-09

    Re: Excel Conditional Formatting

    Post by Ev on Mon Dec 02, 2013 3:14 pm

    Ah ok I forgot to say that I already got the first part done (the one you just wrote). But then I needed the amount of months from now until March 31, whcih I added. It's all good now.

    Sponsored content

    Re: Excel Conditional Formatting

    Post by Sponsored content


      Current date/time is Tue May 23, 2017 8:37 am