I have a spreadsheet in Excel 2007 I use to project "future values", based on weekly (historical) inputs. Each
week I add the last week's numbers, and it updates the projected "future values" column. So the "future
values" column changes a little every week.
I also have a set of "milestone" values. For example,
the set (500, 750, 1038, 1247, 2563, etc.) offers some "milestone" values for the sake of discussion - I might have
fewer or more milestones in a week.
I would like to conditionally format my "future values" column to
highlight the single cells that are "closest to/greater than" each of my "milestones".
conditional format formula should compare the "future values" column against each value in a set of
"milestones", and highlight each single cell closest/higher than each of the "milestones". It should
answer the question "which week will I meet or break the '500' milestone? Ahh...week 12. The '750' milestone? I
see...week 23. The 'xyz' milestone?..." The milestones don't each need to be formatted differently...they could all be
blue or whatever, just so they "pop".
I have used the following conditional format formula, however it
does not meet all the requirements:
where "B" is the
future values column, "750" is a milestone, and "TRUE" selects nearest value rather than exact value.
1) it doesn't compare against multiple values...the one value is hard-coded into the formula, so can't be changed on
the fly, and
2) it picks the closest/smaller value...not the closest/larger value.
Can someone suggest some syntax that meets
If the milestone '750' in the formula could just be substituted with a range like 'Z1:Z10', that
would be great...not that easy, apparently.