Hello, I have a rather complex calculation formula looking at dates. Here is
my table, col A is the weight of the score, col B is the priority bucket ,
col C is the date the date the item in col B was worked, col D is y/n if
there is a date in Col C which is the date that the item in col B was worked,
col E is a conversion from the y/n in Col D to a number for calculation ,Col
F is total number of prioritys in col B, col G is the total number of items
worked in col B, Col H is the % worked of items in Col B, Col I is the %
times the weight of the tasks (priorities) in col B, and J is supposed to
represent whether or not the items worked in Col B were in order. The gist
of it is that these are insurance claims, and they are put on a spreadsheet
in order, the person is to work them all in the order by the spreadsheet,
starting with priority 1. If the person, for example, completes all of the
claims in the priority 1 bucket (Col B) they enter in the date they completed
it. Say, 10/1/08. Then, they go onto priority 2 they complete those on
10/1/08. Then, in priority 3 they complete half on 10/1/08 and the other
half on 102/08. And lets say there are only 4 items/claims per priority
bucket in col B. I have to have a Y/N to show whether or not they worked
each bucket in order, and then they get credit of the weight in col A. So,
the person above would get all 40 of the weight in priority 1, they would get
all 30 of the weight for priority 2, and they would get all of the weight for
priority 3 because they did not do any of the items in priorities 4 or 5 AND
they would get credit for priorities 4 and 5 because they worked their
items/claims in the order they were supposed to.

Now, lets say you have someone that does not follow the order. They work
all of the items in priority 1 on 10/01/08. They work all of the items in
priority 2 on 10/2/08, they start with priority 3 items but they are getting
bored so they stop in priority 3 and jump down to priority 5 and complete
those items in priority 5. Starting at the top, their scoring would be that
they get all of the 40 weight in priority 1 because they did those first and
they completed them. They get the same, full credit, of the weight in
priority 2 because they completed those next. Then, we come down to priority
3, its not finished, so you have to look down to see if anything was done in
priority buckets 4 and 5 and because they show a date of 10/1/08, we know
that they stop in priority 3, jumped to priority 5, thus they did not work
the spreadsheet like they were supposed to. So, this person gets full credit
of the weight for priority 1 and 2, but they are not going to get the credit
for the items they completed in priority 3 or 5 because they did not follow
it in the order they were supposed to.

So far, I have my tool to calculate Col D, E, F, G, H, and I. At the end of
the day, I want to say, "For priority bucket 1, if there is a date in
priority bucket 2 that is less than the date in priority bucket 2, than N, if
there is a date in priority bucket 3 that is less than the date in bucket 1,
then N, if there is a date in priority bucket 4 that is less than priority
bucket 1 than N, if there is a date in priority bucket 5 that is less than
priority bucket 1 than N, and so on. Here is a visual aid,
Col A = Weight - 40 for priority 1, 30 for priority 2, 20 for priority 3,
and 5 for priorities 4 and 5. Thsi will be the scoring in the end.
Col B = Priority, and lets say there are 4 items for each priority 1-5.
Col C = Date, the persin is to enter the date they worked the item
Col D = Y/N, was the item in the priority worked,
Col E = 1/0, 1 meaning yes, it was worked and 0 meaning no it was not and is
just a conversion for the Y/N in Col C.
Col F = calculates Total # of Priorities items (priority 1 has 4 items,
priority 2 has 4 items, and so on.
Col G = calculates Total Worked, ex since they worked all 4 in priority 1
its 100%
Col H - % worked of the items, so example priority 1 would be 100%
Col I = Calculates the percentage times the weight to give them a score
Col J = would be where I start showing weather or not the person did them in
order. How can I write something like, IF B:B=1, and where H:H 1%, and
where B:B=2 and where H:H = 0%, and where B:B=2 and where H:H=0% and the same
down for the other 3 buckets than Y, the person followed the order? I think
I have to tell it to look at the buckets below for each priority. I've tried
various strings of formula, but I cant seem to figure this one out as its
very perplexing. With the tool I've created thus far meets all of what the
top want to see, I just need that last piece of calculating wether or not the
items were worked acccording to prioritization. Thanks - Wendy