Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find %ontime & SUMIF ontime ie: find matching sets within Range...
I tried to simplify this question, but I think the real problem is as simple
as this is going to get. Probably why I couldn't find an answer. I'm looking for dates that are due & complete to... 1) compare projects due&delivered / projects due (%) 2) compare all projects delivered / projects due (%) 3) add up the value of all due projects. ($) 4) add up the value of all delivered projects. ($) I don't want to enter functions row by row and then COUNTIF... I'm working from another worksheet, and don't want to add Columns into the Data Set I'm searching. I want to compare 3 ranges to find what's due in the specified month, and if they were on-time or early. I have 6 cells that calculate the 1st and last day of 'last month', 'this month', 'next month' that I'm using to set my lookup ranges, but I can't count up the matches yet. I have successfully calculated items that are 'due' using =SUMPRODUCT(('PROJECT LIST'!$Y$49:$Y$240=$AD$2)*('PROJECT LIST'!$Y$49:$Y$240<=$AE$2)) I have 6 columns of data, or more specifically 3 sets of 2... A&B, C&D, E&F (each set referring to a project phase) A B C D E F G Due Actual Due Actual Due Actual Value 03/20/08 02/05/08 04/10/08 04/10/08 05/15/08 04/30/08 $10 03/06/08 03/06/08 04/15/08 04/20/08 06/15/08 $50 04/10/08 03/20/08 04/20/08 05/20/08 $20 Anticipated output assuming it's the end of April... Last Month (How did we do in March?) 1) 2 due & complete / 2 due = 100% of committed 2) 3 delivered / 2 due = 150% on-time 3) $60 value due 4) $80 value progressed This Month (How are we looking in April?) 1) 2 due & complete / 4 due = 50% of committed 2) 2 delivered / 4 due = 50% on-time 3) $80 value due 4) $30 value progressed Next Month (What's comming in May?) 1) 1 due & complete / 2 due = 50% of committed (so far) 2) 1 delivered / 2 due = 50% on-time (Hows the remaining project looking?) 3) $30 value due 4) $10 value already delivered |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find %ontime & SUMIF ontime ie: find matching sets within Range...
So, for due&delivered / due just have
=sumproduct(--(A2:F5000=start date),--(A2:F5000<=end date))/(sumproduct(--(B2:B5000=start date),--(B2:B5000<=end date))+sumproduct(--(D2:D5000=start date),--(D2:D5000<=end date))+sumproduct(--(F2:F5000=start date),--(F2:F5000<=end date))) For compare all projects delivered / projects due (%) =(sumproduct(--(A2:A5000=start date),--(A2:A5000<=end date))+sumproduct(--(C2:C5000=start date),--(C2:C5000<=end date))+sumproduct(--(E2:E5000=start date),--(E2:E5000<=end date)))/(sumproduct(--(B2:B5000=start date),--(B2:B5000<=end date))+sumproduct(--(D2:D5000=start date),--(D2:D5000<=end date))+sumproduct(--(F2:F5000=start date),--(F2:F5000<=end date))) add up the value of all due projects. ($) =sumproduct(--(A2:A5000=start date),--(A2:A5000<=end date),--(G2:G5000))+sumproduct(--(C2:C5000=start date),--(C2:C5000<=end date),--(G2:G5000))+sumproduct(--(E2:E5000=start date),--(E2:E5000<=end date),--(G2:G5000)) add up the value of all delivered projects. ($) sumproduct(--(B2:B5000=start date),--(B2:B5000<=end date),--(G2:G5000))+sumproduct(--(D2:D5000=start date),--(D2:D5000<=end date),--(G2:G5000))+sumproduct(--(F2:F5000=start date),--(F2:F5000<=end date),--(G2:G5000)) "Chris T-M" wrote: I tried to simplify this question, but I think the real problem is as simple as this is going to get. Probably why I couldn't find an answer. I'm looking for dates that are due & complete to... 1) compare projects due&delivered / projects due (%) 2) compare all projects delivered / projects due (%) 3) add up the value of all due projects. ($) 4) add up the value of all delivered projects. ($) I don't want to enter functions row by row and then COUNTIF... I'm working from another worksheet, and don't want to add Columns into the Data Set I'm searching. I want to compare 3 ranges to find what's due in the specified month, and if they were on-time or early. I have 6 cells that calculate the 1st and last day of 'last month', 'this month', 'next month' that I'm using to set my lookup ranges, but I can't count up the matches yet. I have successfully calculated items that are 'due' using =SUMPRODUCT(('PROJECT LIST'!$Y$49:$Y$240=$AD$2)*('PROJECT LIST'!$Y$49:$Y$240<=$AE$2)) I have 6 columns of data, or more specifically 3 sets of 2... A&B, C&D, E&F (each set referring to a project phase) A B C D E F G Due Actual Due Actual Due Actual Value 03/20/08 02/05/08 04/10/08 04/10/08 05/15/08 04/30/08 $10 03/06/08 03/06/08 04/15/08 04/20/08 06/15/08 $50 04/10/08 03/20/08 04/20/08 05/20/08 $20 Anticipated output assuming it's the end of April... Last Month (How did we do in March?) 1) 2 due & complete / 2 due = 100% of committed 2) 3 delivered / 2 due = 150% on-time 3) $60 value due 4) $80 value progressed This Month (How are we looking in April?) 1) 2 due & complete / 4 due = 50% of committed 2) 2 delivered / 4 due = 50% on-time 3) $80 value due 4) $30 value progressed Next Month (What's comming in May?) 1) 1 due & complete / 2 due = 50% of committed (so far) 2) 1 delivered / 2 due = 50% on-time (Hows the remaining project looking?) 3) $30 value due 4) $10 value already delivered |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find %ontime & SUMIF ontime ie: find matching sets within Rang
OK, you made that look a lot simpler than it was. There was a lot of code to
fill in, but... It worked great! Thank you very much for the detailed response!!! "Sean Timmons" wrote: So, for due&delivered / due just have =sumproduct(--(A2:F5000=start date),--(A2:F5000<=end date))/(sumproduct(--(B2:B5000=start date),--(B2:B5000<=end date))+sumproduct(--(D2:D5000=start date),--(D2:D5000<=end date))+sumproduct(--(F2:F5000=start date),--(F2:F5000<=end date))) For compare all projects delivered / projects due (%) =(sumproduct(--(A2:A5000=start date),--(A2:A5000<=end date))+sumproduct(--(C2:C5000=start date),--(C2:C5000<=end date))+sumproduct(--(E2:E5000=start date),--(E2:E5000<=end date)))/(sumproduct(--(B2:B5000=start date),--(B2:B5000<=end date))+sumproduct(--(D2:D5000=start date),--(D2:D5000<=end date))+sumproduct(--(F2:F5000=start date),--(F2:F5000<=end date))) add up the value of all due projects. ($) =sumproduct(--(A2:A5000=start date),--(A2:A5000<=end date),--(G2:G5000))+sumproduct(--(C2:C5000=start date),--(C2:C5000<=end date),--(G2:G5000))+sumproduct(--(E2:E5000=start date),--(E2:E5000<=end date),--(G2:G5000)) add up the value of all delivered projects. ($) sumproduct(--(B2:B5000=start date),--(B2:B5000<=end date),--(G2:G5000))+sumproduct(--(D2:D5000=start date),--(D2:D5000<=end date),--(G2:G5000))+sumproduct(--(F2:F5000=start date),--(F2:F5000<=end date),--(G2:G5000)) "Chris T-M" wrote: I tried to simplify this question, but I think the real problem is as simple as this is going to get. Probably why I couldn't find an answer. I'm looking for dates that are due & complete to... 1) compare projects due&delivered / projects due (%) 2) compare all projects delivered / projects due (%) 3) add up the value of all due projects. ($) 4) add up the value of all delivered projects. ($) I don't want to enter functions row by row and then COUNTIF... I'm working from another worksheet, and don't want to add Columns into the Data Set I'm searching. I want to compare 3 ranges to find what's due in the specified month, and if they were on-time or early. I have 6 cells that calculate the 1st and last day of 'last month', 'this month', 'next month' that I'm using to set my lookup ranges, but I can't count up the matches yet. I have successfully calculated items that are 'due' using =SUMPRODUCT(('PROJECT LIST'!$Y$49:$Y$240=$AD$2)*('PROJECT LIST'!$Y$49:$Y$240<=$AE$2)) I have 6 columns of data, or more specifically 3 sets of 2... A&B, C&D, E&F (each set referring to a project phase) A B C D E F G Due Actual Due Actual Due Actual Value 03/20/08 02/05/08 04/10/08 04/10/08 05/15/08 04/30/08 $10 03/06/08 03/06/08 04/15/08 04/20/08 06/15/08 $50 04/10/08 03/20/08 04/20/08 05/20/08 $20 Anticipated output assuming it's the end of April... Last Month (How did we do in March?) 1) 2 due & complete / 2 due = 100% of committed 2) 3 delivered / 2 due = 150% on-time 3) $60 value due 4) $80 value progressed This Month (How are we looking in April?) 1) 2 due & complete / 4 due = 50% of committed 2) 2 delivered / 4 due = 50% on-time 3) $80 value due 4) $30 value progressed Next Month (What's comming in May?) 1) 1 due & complete / 2 due = 50% of committed (so far) 2) 1 delivered / 2 due = 50% on-time (Hows the remaining project looking?) 3) $30 value due 4) $10 value already delivered |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find %ontime & SUMIF ontime ie: find matching sets within Rang
Thank you for the response!
There may have been a more concise answer, but sometimes it's better if you understand how it works so you can do it for yourself next time. "Chris T-M" wrote: OK, you made that look a lot simpler than it was. There was a lot of code to fill in, but... It worked great! Thank you very much for the detailed response!!! "Sean Timmons" wrote: So, for due&delivered / due just have =sumproduct(--(A2:F5000=start date),--(A2:F5000<=end date))/(sumproduct(--(B2:B5000=start date),--(B2:B5000<=end date))+sumproduct(--(D2:D5000=start date),--(D2:D5000<=end date))+sumproduct(--(F2:F5000=start date),--(F2:F5000<=end date))) For compare all projects delivered / projects due (%) =(sumproduct(--(A2:A5000=start date),--(A2:A5000<=end date))+sumproduct(--(C2:C5000=start date),--(C2:C5000<=end date))+sumproduct(--(E2:E5000=start date),--(E2:E5000<=end date)))/(sumproduct(--(B2:B5000=start date),--(B2:B5000<=end date))+sumproduct(--(D2:D5000=start date),--(D2:D5000<=end date))+sumproduct(--(F2:F5000=start date),--(F2:F5000<=end date))) add up the value of all due projects. ($) =sumproduct(--(A2:A5000=start date),--(A2:A5000<=end date),--(G2:G5000))+sumproduct(--(C2:C5000=start date),--(C2:C5000<=end date),--(G2:G5000))+sumproduct(--(E2:E5000=start date),--(E2:E5000<=end date),--(G2:G5000)) add up the value of all delivered projects. ($) sumproduct(--(B2:B5000=start date),--(B2:B5000<=end date),--(G2:G5000))+sumproduct(--(D2:D5000=start date),--(D2:D5000<=end date),--(G2:G5000))+sumproduct(--(F2:F5000=start date),--(F2:F5000<=end date),--(G2:G5000)) "Chris T-M" wrote: I tried to simplify this question, but I think the real problem is as simple as this is going to get. Probably why I couldn't find an answer. I'm looking for dates that are due & complete to... 1) compare projects due&delivered / projects due (%) 2) compare all projects delivered / projects due (%) 3) add up the value of all due projects. ($) 4) add up the value of all delivered projects. ($) I don't want to enter functions row by row and then COUNTIF... I'm working from another worksheet, and don't want to add Columns into the Data Set I'm searching. I want to compare 3 ranges to find what's due in the specified month, and if they were on-time or early. I have 6 cells that calculate the 1st and last day of 'last month', 'this month', 'next month' that I'm using to set my lookup ranges, but I can't count up the matches yet. I have successfully calculated items that are 'due' using =SUMPRODUCT(('PROJECT LIST'!$Y$49:$Y$240=$AD$2)*('PROJECT LIST'!$Y$49:$Y$240<=$AE$2)) I have 6 columns of data, or more specifically 3 sets of 2... A&B, C&D, E&F (each set referring to a project phase) A B C D E F G Due Actual Due Actual Due Actual Value 03/20/08 02/05/08 04/10/08 04/10/08 05/15/08 04/30/08 $10 03/06/08 03/06/08 04/15/08 04/20/08 06/15/08 $50 04/10/08 03/20/08 04/20/08 05/20/08 $20 Anticipated output assuming it's the end of April... Last Month (How did we do in March?) 1) 2 due & complete / 2 due = 100% of committed 2) 3 delivered / 2 due = 150% on-time 3) $60 value due 4) $80 value progressed This Month (How are we looking in April?) 1) 2 due & complete / 4 due = 50% of committed 2) 2 delivered / 4 due = 50% on-time 3) $80 value due 4) $30 value progressed Next Month (What's comming in May?) 1) 1 due & complete / 2 due = 50% of committed (so far) 2) 1 delivered / 2 due = 50% on-time (Hows the remaining project looking?) 3) $30 value due 4) $10 value already delivered |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
.ontime | Excel Discussion (Misc queries) | |||
OnTime bug? | Excel Discussion (Misc queries) | |||
OnTime VB | Excel Discussion (Misc queries) | |||
OnTime VB | Excel Worksheet Functions | |||
Can someone explain OnTime? | Excel Discussion (Misc queries) |