A staying on schedule formula:

[Mark]

See if you can follow this amatuer please.

The formula I need is for cell N7

1. Cells C7:M7 will be numbers from "dates" subtracted in the two cells
directly above. C7:M7 will total in N7

2. I can use conditional formatting to turn text red/green if C7 is more or
less than 14. (actual numbers below)

3. C7 uses 14 days (C7 will turn red/green if more or less than than 14
4. D7 uses 7 days (D7 will turn red/green if more or less than than 7

(OK, on schedule so far thru 2 process's 21days or less.
Box N7 needs to remain green. Even if C7 is 16(red) and D7 is 5(green) (21)
I'm still on schedule in N7 (Green)

Across my cells C7:M7 these are my actual scheduled numbers:

C7 D7 E7 F7 G7 H7 I7 J7 K7 L7 M7 N7
14 7 7 14 7 7 7 7 7 7 14 Total

If you follow my process any cell could be red or green. Over or under the
alotted number below. But if I fall behind and go red, and make up the time
to be on or under the total to that process or cell, I would like N7 to be
Red or Green, On schedule or off.

I wish I could say this easier. It's a running total and I can catch up or
fall behind any of the 11 days. So it's like
1 checks 1. to N7
2 checks 1 and 2, to N7
3 checks 1,2,3
4 checks 1,2,3,4 and so on.

N7 is the key cell to tell me if "overall" I am on schedule.

Example: Day 7 (I7) = allows for 59 days Regardless of the previous 6
entries if I am 59 or below N7 would show Green, over 59 Red.

Wow. Now I have a headache.

Do you too?

Thank you for looking. Sorry if what I ask can't be done.

You guys have ALWAYS been great. And your expertise is admired and gracious.

Mark

 

[JeffP->]

My confidence understanding your business rules is weak, but you may wish to
consider for the respective C7 - M7 a lookup list on another sheet
referencing the respective task class target days to complete or similar.
Then if a particular cell exceeds this limit per it's score would be 1,
whereas if it were w/in the limiter it would be zero, further a total score
of "n" for any row (groups or class of of tasks) could be further scored as
anything that is "n" days over would be w/in compliance and any over would
again score a 1, ok I feel your pain, perhaps you could leave out the
coloring and just reduce it to a boolean and do the make pretty portion of
this project once you have a working model.....

 

[Mark]

I agree I can make pretty after the fix. Now if I only understood in laymens
terms what you meant. I completely understand that this is a language unto
itself, and takes years of experience. Although I can do basic things, I
strive to learn and do much greater things.

Thanks Mark