CODEWITHSUNDEEP
exceltimeexcel-formulatargetconditional-formatting
Shaun Casey
Shaun Casey

Reputation: 69

Using colour to show progress against a target

I have a spreadsheet that has:

 A1 - Target Revenue For Month
 B2 - Daily Revenue
 C2 - MTD Revenue

I want conditional formatting (red/green) to show whether the MTD Revenue is on target to achieve the Target Revenue For Month. For example, if the Target Revenue For Month is £1,000, and this is divided by 30 days = £33.33 per day.

The conditional formatting should show if still on target (achieving more that £33.33 per day on average).

Upvotes: 0

Views: 1270

Answers (1)

pnuts
pnuts

Reputation: 59485

Assuming B2:B31 is populated with a formula such as: 

=A$1*(ROW()-1)/(EOMONTH(TODAY(),0)-EOMONTH(TODAY(),-1))

and that C2:C31 is filled green with standard fill (just to make the Conditional Formatting simpler - can be adjusted to achieve no fill on days without yet an MTD value), then please try:

  • Select ColumnC
  • HOME > Styles - Conditional Formatting, New Rule...,
  • Use a formula to determine which cells to format

  • Format values where this formula is true:

    =AND(C1<B1,C1<>"")

  • Format...

  • Fill
  • Select red colour
  • OK
  • OK

If you would rather cells remain without fill until a value for MTD is entered into them, please skip the standard green fill and instead apply an addition CF rule, in a way similar to above, (but selecting green colour rather than red) of: 

=AND(C1>=B1,B1<>"")

You might also wish to limit the range to which the CF formula rule or rules is applied (seemingly will never need to apply beyond Row 31) but if selecting say C1:C31 please do so from the top down (C1 the active cell) or set the range under Applies to.

Upvotes: 1

Related Questions