Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using function to change a cell color
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using function to change a cell color
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 :-) "Tom" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using function to change a cell color
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Tom" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using function to change a cell color
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. "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Tom" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using function to change a cell color
On Sun, 24 May 2009 10:35:00 -0700, Tom wrote:
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using function to change a cell color
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" wrote: On Sun, 24 May 2009 10:35:00 -0700, Tom wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using function to change a cell color
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using function to change a cell color
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using function to change a cell color
On Sun, 24 May 2009 17:09:01 -0700, Tom wrote:
it worked!! Thx a ton Ron!! You're welcome. Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function to change cell color | Excel Worksheet Functions | |||
How do I change the color of a cell depending on the result of the function in that cell | Excel Programming | |||
use if function to change cell color | Excel Worksheet Functions | |||
Change cell fill color from function | Excel Programming | |||
IF function to change color of cell? | Excel Worksheet Functions |