Conditional Forumla with Forumla assigned- Help needed

Hi All,

Hope your all well? Hopefully this is a clear and easy request.

I have a sports spreadsheet, which changes weekly. The purpose of my post is that i am struggling to format cells accordingly (if it is possible).

I have cell b5, which is a maximum taken over a 52 week period (52 weeks are to the right).
I have cell R5, which is week 15 (where we have picked off on the calendar year)
What id like to do is have cell R5 conditionaly format based on a couple of rules, this would be taken again cell b5 (the max).

I believe this would need to be done by a forumla as the max number can obviously change if it is beaten during the weekly testing.
The rules would be.

Anything from the max score -3 would be green.
Anything between -3 and -6 of the max score would orange.
Anything -6 and below the max score would be red.

Say player one's maximum is 70, week 1 he scores the 70 (green), week 2 he scores 68 (green), week 3 he scores 60 (red), week 4 he scores 72 (green, but week 2 would change to orange accordingly)

The more i look into it the more i dont believe its possible.

Any help or suggestions would be greatly appreciated.

Hi,

In B5, put the following formula:

Formula:

=MAX($C5:$BB5)

This will cause the maximum to change dynamically. Then highlight cells C5:BB5 and down as many rows as you want/need, and go to Conditional Formatting --> Use a formula to determine which cells to format, and enter this formula:

Formula:

=$B5-C5<=3

and format it green, then add a new rule with this formula:

Formula:

=AND($B5-C5>3,$B5-C5<=6)

and format it orange, then add a third rule with this formula:

Formula:

=$B5-C5>6

then format it red.

Then when the maximum changes, so does the formatting.

If you are having trouble making this work, please upload your workbook and I will help you out

Absolute genius! Just applied all that and works fantastically, didnt think it would actually be possible given the exact criteria. Aboslutely thrilled. Will really help the work i am doing. Many many thanks.

You're welcome! Happy to help

Please don't forget to mark this thread as solved