# 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

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.

#### Attachments

• 14.5 KB Views: 27

#### Yorkshire Guy

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

• 11.5 KB Views: 46

#### kets23

Hi Yorkshire Guy

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.

As Seen On