Thanks:  0
Likes:  0

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

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

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

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

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•