Excel Formula

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Attachments

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
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top