Comparing figures against target formula

[halesowenmum]

Hi

I need a forumula for the following.

In columns going left to right I've got dates populated, one day per column. On each of the Mondays (or Tuesday of the week, if that week had a Bank Holiday Monday!) there are target values showing that are the values that should be achieved at those particular dates when we take and record the measurements.

It may be that we're wanting to reduce something eg hours spent driving to and from meetings because we're doing more meetings through Skype. However, the goal might not be to reduce, it may be to increase - so for example if we wanted more items processed in a day. So the formula needs to 'know' or be structured so it can cope with both (hope that makes sense!). I don't mind having a cell where I put what the Goal is ie 'reduce' or 'increase' if that would help.

I would like the cell below the cell where the result of this formula will be displayed to make it clear where they are with progress by expressing 'On track', 'Behind' or 'Ahead', wiith none of those nasty ######. I can then use CF to colour it up and make it obvious if they're making the right progress or not.

In the spreadsheet I'm working in at the mo, it's cells M10, R10, W10, AB10, AG10 and AL10 where this wording and my CF need to come together so the users can instantly see how well they're doing.

Can anyone kindly help with a formula for this?

 

[RasGhul]

Can you post some sample data?

 

[halesowenmum]

Can you post some sample data?

Here we go as best as I can without trying to post an actual spreadsheet:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Explanation column[/TD]
[TD][/TD]
[TD]L[/TD]
[TD]Q[/TD]
[TD]V[/TD]
[/TR]
[TR]
[TD]

User manually enters hrs:mins spent on the activity - actual figures, every Monday. So Col L shows a result where they've not quite achieved getting down to the value of 05:08 that's referred to in row 6, Col Q shows an example where they're bang on track, Col V shows an example where they're ahead of the game and have achieved more hrs:mins time savings than was hoped for​

[/TD]
[TD]4[/TD]
[TD]05:15[/TD]
[TD]

04:46​

<strike></strike>
[/TD]
[TD]03:24[/TD]
[/TR]
[TR]
[TD]The value they should have achieved at this point which is just a simple formula which is fine as is[/TD]
[TD]6[/TD]
[TD]05:08[/TD]
[TD]

04:46​

<strike></strike>
[/TD]
[TD]04:24[/TD]
[/TR]
[TR]
[TD]The formula to calculate how far ahead or behind they are which is NOT working as it's not quite enough for all the on track, behind, ahead scenarios[/TD]
[TD]7[/TD]
[TD]=L4-L6[/TD]
[TD]

=Q4-Q6​

<strike></strike>
[/TD]
[TD]<strike></strike>

=V4-V6​

<strike></strike>
[/TD]
[/TR]
[TR]
[TD]The results of the above formula displayed are:[/TD]
[TD]

7​

<strike></strike>
[/TD]
[TD]00:07[/TD]
[TD]00:00[/TD]
[TD]#####[/TD]
[/TR]
</tbody>[/TABLE]

And of course we'd want to highlight the results in row 7 with if they're behind (if possible this behind bit needs a bit of granularity to it) so if by 50% or more behind its shaded red, if they're behind 49% or less its shaded amber, if they on track it's green (ie value entered is the same as value planned), and if they're ahead then bright green with bells on because they've achieved more than was hoped (all through whatever is the best way to do it using CF).