Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF AND calculations with date
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF AND calculations with date
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF AND calculations with date
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$16B2,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 =(C20)*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 ** Please remember, if your question is answered, to check the YES box below. It helps everyone. "Ranking by type, and the dashes" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF AND calculations with date
That being said, after working on this problem, some feedback from the OP
would have been nice :) -- ** John C ** Please remember, if your question is answered, to check the YES box below. It helps everyone. "John C" wrote: 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$16B2,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 =(C20)*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 ** Please remember, if your question is answered, to check the YES box below. It helps everyone. "Ranking by type, and the dashes" wrote: 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF AND calculations with date
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" wrote: That being said, after working on this problem, some feedback from the OP would have been nice :) -- ** John C ** Please remember, if your question is answered, to check the YES box below. It helps everyone. "John C" wrote: 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$16B2,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 =(C20)*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 ** Please remember, if your question is answered, to check the YES box below. It helps everyone. "Ranking by type, and the dashes" wrote: 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF AND calculations with date
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 :) -- ** John C ** "Ranking by type, and the dashes" wrote: 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" wrote: That being said, after working on this problem, some feedback from the OP would have been nice :) -- ** John C ** Please remember, if your question is answered, to check the YES box below. It helps everyone. "John C" wrote: 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$16B2,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 =(C20)*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 ** Please remember, if your question is answered, to check the YES box below. It helps everyone. "Ranking by type, and the dashes" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Date calculations | Excel Worksheet Functions | |||
More date calculations | Excel Worksheet Functions | |||
Date Calculations before date entered | Excel Discussion (Misc queries) | |||
Date calculations and sum | Excel Worksheet Functions | |||
Date Calculations | New Users to Excel |