it worked!! Thx a ton Ron!!

"Ron Rosenfeld" wrote:

On Sun, 24 May 2009 12:10:08 -0700, Tom wrote:

I will be adding data everyday and am looking for a formula/method to do
this without putting intermediate calculations/columns etc

As Bernard has already written, you CANNOT do this with a FORMULA.

You CAN do this with CONDITIONAL FORMATTING.

------------------------------
for the first 15 days if a new student scores 90% or lesser of what old
students score (avg) he is marked RED (anything above that is GREEN)
for the next 15 days (15-30) if a new student scores 92.5% or lesser of what
old students score (avg) he is marked RED (anything above that is GREEN)
for the next 15 days (30-45) if a new student scores 95% or lesser of what
old students score (avg) he is marked RED (anything above that is GREEN)
for the next 15 days (45-60) if a new student scores 97.5% or lesser of what
old students score (avg) he is marked RED (anything above that is GREEN)
post 60 days if a new student scores 100% or lesser of what old students
score (avg) he is marked RED (anything above that is GREEN)
------------------------------

Just extend the conditional formatting that I posted earlier, to encompass your
variations.

I don't see anything in this list regarding Yellow, yet you had it in your
other list.

Also, in addition to being complete, it would help (and help you also) to be
specific.

In your specifications above, you have overlapping ranges. So depending on how
you write your formulas, you may see different results for students at 15, 30
or 45 days.

One method of writing the CF formula for red might be:

=OR(AND((CurrentDate-StartDate)<=15,CurrentValue<=(90%*DayAverage)),
AND((CurrentDate-StartDate)<=30,CurrentValue<=(92.5%*DayAverage)),
AND((CurrentDate-StartDate)<=45,CurrentValue<=(95%*DayAverage)),
AND((CurrentDate-StartDate)<=60,CurrentValue<=(97.5%*DayAverage)),
AND((CurrentDate-StartDate)60,CurrentValue<=DayAverage))

And for green:

=OR(AND((CurrentDate-StartDate)<=15,CurrentValue(90%*DayAverage)),
AND((CurrentDate-StartDate)<=30,CurrentValue(92.5%*DayAverage)),
AND((CurrentDate-StartDate)<=45,CurrentValue(95%*DayAverage)),
AND((CurrentDate-StartDate)<=60,CurrentValue(97.5%*DayAverage)),
AND((CurrentDate-StartDate)60,CurrentValueDayAverage))

Because of the way I wrote those formulas, they are not "exclusive", so the
formula for GREEN must be listed prior to the formula for RED.

You could rewrite them so the order wouldn't make any difference, but that
would make them more complex.
--ron