# Excel Formula

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

Not open for further replies.

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.

File size:
14.5 KB
Views:
27
2. ### 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:

• ###### Example(1).xls
File size:
11.5 KB
Views:
46

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

As Seen On