IF AND calculations with date

[Ranking by type, and the dashes]

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

 

[Bernard Liengme]

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"

 

[John C]

With all due respect to Bernard, I disagree. I do concede his point, however,
as you were overly verbose and gave some detail that wasn't needed. That
being said, I saw the long post, and considered it a challenge. To re-cap
what you are 'essentially' needing:
For each line, you need to find out of the person is eligible to receive the
Scored weight for that line or not. In order to receive credit for the scored
weight, all priorities that match that priority number must have been
completed before (or same day of) any lower priority, and, conversely, you
can't have worked on any priority until after (or same day of) all higher
priorities have been worked on. As a summary, to use your example of 4
different lines for each priority of 1-5 (total 20 lines).
Priority 1s must be completed before all priorities 2-5, to receive credit
for Priority 1s.
Priority 2s must be started after priority 1 is complete, and completed
before starting 3-5.
Priority 3s must be started after priorities 1-2 are complete, and completed
before 4-5 are started.
Priority 4s must be started after priorities 1-3 are complete, and completed
before 5 is started.
Priority 5 must be started after priorities 1-4 are complete.

To confirm, 1 bad decision can ruin everything. For example, Say I had the
following dates for each priority:
1: 10/1, 10/2, 10/3, 10/4
2: 10/5, 10/6, 10/7, 10/8
3: 10/9, 10/10, 10/11, 10/12
4: 10/13, 10/14, 10/15, 10/16
5: 10/17, 10/18, 10/19, 10/20
Now, say that the first item under priority 5, that I felt like doing on
10/2, instead of 10/17. Well, by my understanding, they would get no credit
for any of it, because I did not complete any priority(1, 2, 3, 4) before I
started a lesser priority, and I don't get credit for priority 5 either
because I started it before I completed 1-4.

Here is my formula, I have subsituted a YES for receives credit, and NO for
doesn't receive credit, modify as you need to. Note: This formula is an array
formula, and therefore must be entered into the cell with CTRL+SHIFT+Enter:
=IF(AND(OR(B2=5,MAX(IF($B$2:$B$16=B2,$C$2:$C$16))<=MIN(IF($B$2:$B$16>B2,IF($C$2:$C$16<>0,$C$2:$C$16)))),OR(B2=1,MIN(IF($B$2:$B$16=B2,IF($C$2:$C$16<>0,$C$2:$C$16)))>=MAX(IF($B$2:$B$16<B2,$C$2:$C$16)))),"Yes","No")
Modify the range as necessary to incorporate your dataset.

Because of the extensive detail, I also noticed some extra columns that are,
to my understanding, unnecessary.
For example, you have a formula to convert Y/N to 1 or 0, and then you use
the 1 or 0 in calculations. Instead of saying =E2*someformula, you could just
put, =(D2="Y")*someformula. You may even take it a step further, as the Y/N
is based on if there is a date in column C, so you could just use
=(C2>0)*someformula
This assumes that the only valid entries in Column C is if it is blank/0
(not worked on) and a date saying it has been worked on.

 

[John C]

That being said, after working on this problem, some feedback from the OP
would have been nice

 

[Ranking by type, and the dashes]

My Dear John, I am so sorry I havent written back sooner... I LOVE LOVE LOVE
what you wrote, and then some!! I have been pushing to get this project out
the door and I completed it late last night. I tested multiple scenario's
and its VERY cool. I totally understood what you said, and wrote. I agree
with you, some of the cols were not needed. Please forgive me, I'm a new
poster and had never been to the online help. Bernard was correct, I can be
verbose, and I will try to watch that in the future.

Thank you so very much for your help and everyone out here!! I appreciate it!

Wendy

 

[John C]

Not a problem. Seriously. I did like the challenge, and when I hadn't heard
anything, didn't know if you ever tried the solution. Many people who seek
help get the help, but never let the responder know that the help actually
helped. I am glad it worked for you. And remember, we were all new once. Part
of the reasons I am here is not only do I enjoy helping others when I can,
but there are some real serious users on here who really know their stuff.
And I love to learn new things to.

Anyway, as you can see, I am rather verbose myself. Glad it helped, and
never fear asking questions here. There are many other responders who are
really really really good.
Please remember to check the yes box below