Solved

Excel formula help

Posted on 2014-11-19
10
90 Views
Last Modified: 2015-01-19
Hello, I have an excel spreadsheet with a great deal of data.  I'm looking for a few clean formulas to make the process better/work.  

there overall report is about 8000 line items detailing task assignment.  The current spreadsheet includes: Department, Task assignment date, task compeltion date.  Please note the task is due 20 days after open.  

So, What i need to pull is a report/sheet with the following columns of information:

Department  
-Number of Task by Department  
-Number completed by due date  
-Number still open (no completion date)  
-Number NOT competed on time (includes those that were compelted after the 20day due date and those not completed at all(no completion date))
-Percentage of tasks completed late (includes not compelted)
0
Comment
Question by:LadyHagood
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 47

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 40453398
Can you post an example workbook showing the layout of your data to enable us to better understand your requirements?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 40454230
Howdy Wayne! :)
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40455323
Here's a cobbled-together summary of what I think you're after.

I've done the Department Count
Not Completed count, and
Completed On Time count.

presumably for the Not Completed On Time count, you'd want to EXCLUDE projects that were still within their 20-day limit?

I also made an assumption that you only work Mon-Fri in all date calcs.
M--Personal-ee---task-schedule.xlsx
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 23

Expert Comment

by:Danny Child
ID: 40455325
Will try and stop by later, but gotta go now...
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40455349
I also set all dates in a UK format - ddd dd-mm-yy

 - you may want to change this if your location's different...
0
 
LVL 23

Accepted Solution

by:
Danny Child earned 500 total points
ID: 40455436
found some time, try this:

In Col F - "Actual", you'd have the formula set for all new tasks.  Then, overwrite this with the final completion date when it happens.

"spare" copy of the formula in cell F24, in case you ever fill the table with dates...!
M--Personal-ee---task-schedule---v2.xlsx
0
 
LVL 23

Expert Comment

by:Danny Child
ID: 40479600
Hi, is there any update on this?  Thought I'd covered all the bases here, pretty much???
0
 
LVL 42

Expert Comment

by:pcelba
ID: 40515557
This is purely database task so if you connect your sheet to any SQL database then you may execute a few simple queries:

-Number of Task by Department
SELECT Department, COUNT(*) FROM YourDataTable GROUP BY Department

Open in new window

-Number completed by due date
SELECT CompletionDate, COUNT(*) FROM YourDataTable GROUP BY CompletionDate

Open in new window

-Number still open (no completion date)
SELECT COUNT(*) FROM YourDataTable WHERE CompletionDate IS NULL

Open in new window

-Number NOT competed on time (includes those that were completed after the 20day due date and those not completed at all(no completion date))
SELECT COUNT(*) FROM YourDataTable WHERE CompletionDate IS NULL OR CompletionDate-StartDate > 20

Open in new window

-Percentage of tasks completed late (includes not compelted)
You may simply divide the number of tasks from the previous step by total number of tasks. 
I would also recommend to exclude not completed tasks before 20 days period

Open in new window


Of course, some of the above calculations would need clarification or fine tuning. Not all SQL engines support simple date subtraction. Etc.

If you need to report everything by Department simply add Department to the command as you can see in the first SQL example.
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40557302
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question