1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Formula

Discussion in 'Business Applications' started by kets23, Apr 14, 2010.

Thread Status:
Not open for further replies.
  1. kets23

    kets23 Thread Starter

    Joined:
    Apr 13, 2010
    Messages:
    6
    Hi

    Would be grateful for advice on what formula to use for the below.


    1. I am trying to find out the average number of days for each job type completed (i.e. the average number of days for job types emergency, urgent & routine that have been completed). How do I write the conditional statement to do this (assuming that this can be done).
    2. What formula do I use to get a combined average number of days to complete jobs for all completed job types (Emergency, urgent & routine).
    3. Each job type has a target for completion, Emergency has a target date of 3 days for completion, urgent has a target date of 7 days for completion and routine has a target date of 28 days for completion. I would like to count the number of job types that were completed within target. In the example below, two of the routines were completed within target of 28 days, the data for this is the number of days to complete job is caluclated from the date received/date completed. Again, how do I do this.
    I have attached the file that relates to the above.


    Many thanks in advance
     

    Attached Files:

  2. Yorkshire Guy

    Yorkshire Guy

    Joined:
    Dec 9, 2003
    Messages:
    563
    Hi Kets23,

    I've updated your workbook with two techniques.

    1 & 2. To get the averages, easiest is to use Pivot Table - needs to be refreshed whenever you change data in the source.
    This shows you the averages per Job Type and the overall average.

    3. I've used an array formula (needs to be entered with Ctrl + Shift + Enter) to count how many jobs hit target.

    I also named the ranges so make the formulas more understandable.
    Removed two blank columns to aid the Pivot Table.

    Suggest you use HELP to get info on the above techniques.

    lol
    Hew
     

    Attached Files:

  3. kets23

    kets23 Thread Starter

    Joined:
    Apr 13, 2010
    Messages:
    6
    Hi Yorkshire Guy

    Many thanks for your response.
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/916780

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice