Dear Poster,
No one is going to read a message this long and try to work out what is
needed. Try to give us a few simple examples of what you want.
If this is not possible then it would seem you are asking us to do an
complete project rather than just help you with a single task.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ranking by type, and the dashes"
om wrote in message
...
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