Complex Conditional Formatting

[sli]

I have a weekly goal value in cell B4. Cells D4-H4 are populated with value for weeks 1-5 respectively and turn red/green as compared against B4. Cell "I4" sums up values in D4-H4 that are not blank and checks against (B4 * 5) to see if it is over/under. Over = red and under = green.

Problem I have encountered:

B4 = 20 (weekly goal)

D4 = 25 (week 1 value)
E4 = 25 (week 2 value)
F4 = 0 (only week 2, so not populated yet)
G4 = 0 (only week 2, so not populated yet)
H4 = 0 (only week 2, so not populated yet)

I4 = 50 (sum of D4-H4)

I4 will incorrectly show green since 50 is less than (b4*5=100), so what I need the program to do is understand that only 2 weeks have elapsed - making the total days turn red since the total monthly goal should only be 40 (2 weeks into the month) and I4 is now at 50.

Hope I made that as confusing as possible. I can email the spreadsheet. That may better expain what I am trying to say.

Thanks for any help.

Sli

 

[Aladin Akyurek]

On 2002-05-08 17:00, sli wrote:
I have a weekly goal value in cell B4. Cells D4-H4 are populated with value for weeks 1-5 respectively and turn red/green as compared against B4. Cell "I4" sums up values in D4-H4 that are not blank and checks against (B4 * 5) to see if it is over/under. Over = red and under = green.

Problem I have encountered:

B4 = 20 (weekly goal)

D4 = 25 (week 1 value)
E4 = 25 (week 2 value)
F4 = 0 (only week 2, so not populated yet)
G4 = 0 (only week 2, so not populated yet)
H4 = 0 (only week 2, so not populated yet)

I4 = 50 (sum of D4-H4)

I4 will incorrectly show green since 50 is less than (b4*5=100), so what I need the program to do is understand that only 2 weeks have elapsed - making the total days turn red since the total monthly goal should only be 40 (2 weeks into the month) and I4 is now at 50.

Hope I made that as confusing as possible. I can email the spreadsheet. That may better expain what I am trying to say.

Thanks for any help.

Sli

Post your formulas for red and green, so we can adjust them.

 

[sli]

Condition 1

Cell value equal 0 then format white background
Cell value greater than =$B$4*5 then format red
Cell value less than or equal to =$B$4*5 then format green

 

[Aladin Akyurek]

On 2002-05-09 09:51, sli wrote:
Condition 1

Cell value equal 0 then format white background
Cell value greater than =$B$4*5 then format red
Cell value less than or equal to =$B$4*5 then format green

Change

=$B$4*5

to

=($B$4/COUNT($D$4:$H$4))*5

 

[sli]

Made a small change to the formula and it works great.

=($B$4*COUNT($D$4:$H$4))

Interesting to note: If a "0" (zero) is in any of the cells (D4-H4), then the COUNT function includes that cell in the count. I did not know that before.

Thanks
Sli