[SOLVED] How to conditionally format multiple cells, by comparing to another set of v

[vez001]

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".

The 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:

=$B3=((VLOOKUP(750,$B$3:$B$211,1,TRUE)))

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 my criteria?

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.

[rscsmith]

Hi Vez.

I'm struggling to understand this. If I've got it wrong, could you post some sample data? (Use the Go Advanced option below).

Try this for cell B3 and copy down:

=$B2<VLOOKUP($B3,$Z$1:$Z$10,1)

Milestones are in Z1:Z10. The first milestone must be 0, then your values. e.g. 0, 250, 500, 750, ...

It checks whether the previous value is less than the current one's VLOOKUP result.

e.g. of the logic:
This future value is 510, and the previous was 490. The current milestone looks up as 500 (lower than this future value). The previous value is less than this. This only occurs when the two numbers straddle a milestone.

Cheers, Rob.

[vez001]

Rob, that worked nicely. Thanks!

[rscsmith]

No probs. Don't forget to edit your thread title to SOLVED.

Cheers, Rob.