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