You are not connected. Please login or register

 » Alphabet soup + Anouncements! » Random Thoughts - NON Hockey talk » Excel Conditional Formatting

Excel Conditional Formatting

Go to page : 1, 2, 3  Next

Go down  Message [Page 1 of 3]

1Excel Conditional Formatting Empty Excel Conditional Formatting on Fri Nov 29, 2013 3:01 pm

Ev

Ev
Franchise Player
Franchise Player
Anyone good with this stuff?

I'm trying to make the cells in a column turn red, green, or yellow based on other cells.

For example, I want the cell to turn red if Cell A is -30% or lower, or if Cell B is < 12, or if Cell C is a negative value, etc.

2Excel Conditional Formatting Empty Re: Excel Conditional Formatting on Fri Nov 29, 2013 3:22 pm

Cap'n Clutch

Cap'n Clutch
Co-Founder
Co-Founder
It should be easily found on the menu bar no?


_________________
"A child with Autism is not ignoring you, they are waiting for you to enter their world."

- Unknown Author

3Excel Conditional Formatting Empty Re: Excel Conditional Formatting on Fri Nov 29, 2013 3:26 pm

Cap'n Clutch

Cap'n Clutch
Co-Founder
Co-Founder
Excel Conditional Formatting Snap411


_________________
"A child with Autism is not ignoring you, they are waiting for you to enter their world."

- Unknown Author

4Excel Conditional Formatting Empty Re: Excel Conditional Formatting on Fri Nov 29, 2013 3:35 pm

wprager

wprager
Administrator
Administrator
Which version of Excel? Do you want the cell to change based on its own value or based on another cell? Do you want it based on the same cell for all cells where you want the change or based on same row/different column, or same column/different row?

Lots of questions to be answered.

P.S. I *am* good with this stuff.


_________________
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

5Excel Conditional Formatting Empty Re: Excel Conditional Formatting on Fri Nov 29, 2013 3:38 pm

tim1_2

tim1_2
Franchise Player
Franchise Player
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.

6Excel Conditional Formatting Empty Re: Excel Conditional Formatting on Fri Nov 29, 2013 3:46 pm

shabbs

shabbs
Hall of Famer
Hall of Famer
Hmmmm... you're looking for conditional formatting using three different values under three different conditions. You should be able to use a formula that evaluates those cells based on your conditions to determine the resulting colour of the cell you are setting this up in. The formulae would be a series of OR evaluations for each of your three tests.

7Excel Conditional Formatting Empty Re: Excel Conditional Formatting on Fri Nov 29, 2013 3:54 pm

Ev

Ev
Franchise Player
Franchise Player
Here is what I am working with:

Excel Conditional Formatting D79HwCM

I want the column Financial Status to be a certain colour based on the other cells.

H is Balance October 31, K is %change, P is months until 0 funds

I want it to show up green if all three statements are true: H > 0, K > 0%, P > 24

Yellow if H > 0, K is less than 0% but greater than -30%, P is between 12 and 24

Red if H<0, K less than -30%, P less than 12

8Excel Conditional Formatting Empty Re: Excel Conditional Formatting on Fri Nov 29, 2013 4:06 pm

wprager

wprager
Administrator
Administrator
Excel Conditional Formatting Captur14

Sorry, I did the above when I thought it was an "OR" of the three conditions. Just change the formula to AND.

How you get to the rule editing depends a lot on the version of Excel you have but, essentially, configure it to use a formula and use the one I've got (changed to AND):

Code:

=AND($A2<0.3,$B2<12,$C2<0)
The dollar sign in front of the A means to lock-in the column which is checked. That formula was entered (originally) for a cell in row 2, so the $A2 means that if I copy the formatting to a cell in row 4, the formula will change to check $A4. The conditional formatting is applied to the cells in column D.


_________________
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

9Excel Conditional Formatting Empty Re: Excel Conditional Formatting on Fri Nov 29, 2013 4:14 pm

wprager

wprager
Administrator
Administrator
I assume if it doesn't match any of the three rules then it's no cell filling? Then you need three rules.

rule 1 -- to set green:
Code:

=AND($H2>0,$K2>0,$P2>24)
rule 2 -- to set yellow:
Code:

=AND($H2>0,AND($K2<0,$K2>-0.3),AND($P2>=12,$P2<=24))
rule 3 -- to set red:
Code:

=AND($H2<0,$K2<-0.3,$P2<12)
And I'd be stunned if I didn't make a mistake, so you'll likely have to tweak.


_________________
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

10Excel Conditional Formatting Empty Re: Excel Conditional Formatting on Fri Nov 29, 2013 4:15 pm

wprager

wprager
Administrator
Administrator
So set those rules for a cell in row 2, then do a format copy to all other cells. If the cell you are setting it to is not in row 2 then adjust the formulas 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

11Excel Conditional Formatting Empty Re: Excel Conditional Formatting on Fri Nov 29, 2013 4:23 pm

Ev

Ev
Franchise Player
Franchise Player
oh sorry it's 2010...and I might have different conditions, but I'll check on Monday.

Thanks W.P. Rager and all

12Excel Conditional Formatting Empty Re: Excel Conditional Formatting on Fri Nov 29, 2013 4:30 pm

wprager

wprager
Administrator
Administrator
shabbs wrote:Hmmmm... you're looking for conditional formatting using three different values under three different conditions. You should be able to use a formula that evaluates those cells based on your conditions to determine the resulting colour of the cell you are setting this up in. The formulae would be a series of OR evaluations for each of your three tests.
Conditional formatting doesn't work like that. You cannot have a single rule with multiple outcomes. You could do it with VBA, of course, but not conditional formatting.


_________________
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

13Excel Conditional Formatting Empty Re: Excel Conditional Formatting on Fri Nov 29, 2013 4:35 pm

wprager

wprager
Administrator
Administrator
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:
Excel Conditional Formatting Captur15

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.


_________________
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

14Excel Conditional Formatting Empty Re: Excel Conditional Formatting on Fri Nov 29, 2013 5:40 pm

shabbs

shabbs
Hall of Famer
Hall of Famer
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.

15Excel Conditional Formatting Empty Re: Excel Conditional Formatting on Sat Nov 30, 2013 9:30 am

TheAvatar

TheAvatar
Veteran
Veteran
I'm the king of excel Smile

Sponsored content


Back to top  Message [Page 1 of 3]

Go to page : 1, 2, 3  Next

Permissions in this forum:
You cannot reply to topics in this forum