Using function to change a cell color

[Tom]

Hi,

This is what I am doing.
I have 4 variables: StartDate,CurrentDate,CurrentValue,DayAverage
I am passing these four variables into an excel function.

What I want to do is that if the difference between CurrentDate and StartDate
is greater than 30 (1 month) and CurrentValue<DayAverage then the cell from
where I called this function should turn "RED". If the difference is less
than 30 and CurrentValue<DayAverage then the cell should turn "YELLOW".


Changing color from within Function doesnt seem to work. Help!!

Thanks,
Sherry

 

[Tom]

sorry missed this..

btw I am returning the CurrentValue to the cell. In essense I want this
function to just trigger a color change in the cell as per the conditions in
set below..

Thx

 

[Bernard Liengme]

Functions can do one thing and one thing only: return a value to their own
cell (or cells in the case of array formulas)
They cannot do any formatting.

Have you thought of using conditional formatting? Try Help and then come
back with questions (what version of Excel are you using?)
best wishes

 

[Tom]

version 2003
Conditional formatting wont work (i think). As the formatting depends on two
values (one constant = DayAverage and one variable = Difference in days).
Each day a record would be added.

 

[Ron Rosenfeld]

Conditional formatting wont work (i think). As the formatting depends on two
values (one constant = DayAverage and one variable = Difference in days).
Each day a record would be added.

Sure it will work. You just need to use the correct formulas.

For example, for RED:

=AND((CurrentDate-StartDate)>30,CurrentValue<DayAverage)

and for YELLOW:

=AND((CurrentDate-StartDate)<30,CurrentValue<DayAverage)

In Excel 2003, you can have up to 3 conditional formats
--ron

 

[Tom]

Thx Ron - this was good. What I want still isnt happening.
Here's what I am trying to acheive (sorry for not being clear at the
first time)

I am tracking daily performance of a new batch of students. I get scores
daily for old and new students)

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)

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


Thx for all your help Bern & Ron

 

[Ron Rosenfeld]

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,CurrentValue>DayAverage))

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

 

[Tom]

it worked!! Thx a ton Ron!!

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,CurrentValue>DayAverage))

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

 

[Ron Rosenfeld]

it worked!! Thx a ton Ron!!

You're welcome. Glad to help. Thanks for the feedback.
--ron