Excel formula help

Kol

Kol

Kol

Man of Honour
Joined
8 Jan 2003
Posts
14,336
Location
Ashby-de-la-Zouch
Hi guys,

I wonder if any of you gurus can help me? Say along the top of my spreadsheet I have the following:

name - week1 - week2 - week3 (onwards) to week 52

Is there any way I can get excel to compare week2 to week1 and week3 to week2 and so on and then give me a simple RED box or a GREEN box to show whether the value has gone up or down? So it would be:

name - week1 - week2(red/green fill) - week3 (red/green fill) ?

and then at the end say it goes:

week52 - overall (red/green fill)

where the overall basically compares there overall performance with regards to their first week?

Cheers chaps!
 
Col A - Name
Col B - Week 1
Col C - Week 2
etc

in the box you want to show the colour, add conditional formatting for the column, use formula and enter =C2>B2 (make sure you DO NOT use absolute referencing with the $), then select green, add another condition for =C2<=B2 and add red
 
Last edited:
rafster, you may have to elaborate for me mate as I'm not too great with excel so ideally would need it in laymans terms, ie. the formula! :)

Ah you've edited! Thanky you! I'll give that a shot right now :)
 
Last edited:
OK - that's great, it works. Is there anyway of dragging this conditional formatting down?

So a2 works with a3
b2 works with b3

etc. and also dragging it across, so a3 will work with a4, b3 with b4 etc?
 
Kol said:
OK - that's great, it works. Is there anyway of dragging this conditional formatting down?

So a2 works with a3
b2 works with b3

etc. and also dragging it across, so a3 will work with a4, b3 with b4 etc?
Click to expand...

highlight A2, drag the bottom right corner of the selection over the area you want to duplicate the formula for.

Excel will auto set the formula and conditional formatting.
 
That just drags the value that is typed into the box, ie. the 82.5%, not the conditional formatting :(

annoyingly, the values have already been entered (if that causes any problems).
 
OK - I've been playing. I made sure the absolute references were taken out, then I used format painter. Wow, what an awersome tool, both conditional formatting and format painter!

Cheers for your help chaps!
 
Last edited:
you highlight the whole range not one cell

so if you had 100 rows, highlight all 100, then when you do the formulas, you use eg B2 not $B$2 which it will try to use if you mouse select (this will condition format just B2 for all rows, without the $ it will do B3, B4 etc for each subsequent row)

Excel 2007 has some nice graphics for conditional formatting - they look great with Excel Services and online dashboards.
 
You must log in or register to reply here.
Back
Top Bottom