conditional formating in Excel

sheriffwoody

Distinguished Member
having read a few of the replys to help another member with an excel problem i thought i would share my issue.

i need to put some conditional formatting into a spreadsheet, but haven't really used it before.

column a is budget column
column b is actual spend/income column
column c is a variance column (between B and A)

what i need are the cells in column c to turn red if the variance is either £500,000 OR 5% adverse to plan.

this is in old excel by the way (2003 version)

any ideas?
 

GloopyJon

Member
You need to set up the rules shown in the attached screenprint. Make sure that you delete the $ signs that Excel adds in if you select the cells, because otherwise when you copy the formatting to the other rows (C3, D3 etc), it will still refer back to the first row.

Of course, you can set up the formatting as you like, and use the same colour or whatever. I picked two different colours so that I could check that both conditions were working.
 

Attachments

  • Conditional formatting.JPG
    Conditional formatting.JPG
    31.7 KB · Views: 32

Stuey1

Well-known Member
having read a few of the replys to help another member with an excel problem i thought i would share my issue.

i need to put some conditional formatting into a spreadsheet, but haven't really used it before.

column a is budget column
column b is actual spend/income column
column c is a variance column (between B and A)

what i need are the cells in column c to turn red if the variance is either £500,000 OR 5% adverse to plan.

this is in old excel by the way (2003 version)

any ideas?

Conditional formatting in 2003 allows up to 3 different formats, any more than that and you would need VBA.

to do what you have asked would just be using a formula:

=IF(OR(C2>500000,C2>(A2/20)),1,0)

If you enter that formula into the formula bit on conditional formatting it should work altough my logic may be a bit out as i'm kind of guessing what the 5% is of...

I can't test it as i have 2010, but should be ok i think

EDIT: This will only turn the cells red when they are over budget (not if they are 500k or 5% under budget, not sure if it would need to do both.
 
Last edited:

Stuey1

Well-known Member
This approach works - you can test it in 2010 :)

However just FYI the 'if' is not necessary - you can just say:

=OR(C2>500000,C2>(A2/20))

Yep, just tested it in 2010...

didn't know that about the IF, however i don't use conditional formatting much, usually end up having to use VBA.
 

The latest video from AVForums

Maverick UK Premiere IMAX Review + Top Gun, Tom Cruise, Tony Scott and 4K + Movie/TV News
Subscribe to our YouTube channel
Support AVForums with Patreon

Top Bottom