Error when calculating average of times

[Eazygeezer]

Hi,

I have to analyse the time spent on calls and work out an average of those times based on some information in a table.

I have 3 types of call and I would like to be able to get the average time for each of the call types. I have moved the data into columns and tried to use the average function, I believe it works for two sets of data but not the third.

I have two queries:

1. Is it possible to use a formula that can examine data from the table in column 1 i.e. call type 1, reference the length of that call in column 5, add them up and get a correct average?

2. Why is one data set not averaging correctly and how do I get it to do so correctly.

All help and advice very gratefully received.

[Pete_UK]

I'm not sure what your problem is, as the averages on row 108 (columns H to J) are the same as those in cells F310:F312.

You could save yourself a lot of work by making use of the AVERAGIF or AVERAGEIFS functions acting directly on the data in column F, as I suspect you may have copied the data incorrectly across into columns H to J.

Hope this helps.

Pete

[Croweater]

Have a look at cell H17 in your sheet. That may be why you think the average is not working.

[Eazygeezer]

HI,

Thanks for the spot, yes somehow I had missed that H17 value. However even after I have changed it from 19:36:00 to 00:36:00 the average hours still works out at 02:11:33, I just can't see how this is possible when there are no other figures above 01:30:00 and the majority are in fact below 00:30:00.

The other two average calculations appear to work fine, I have scoured column H looking for other anomalies but I can't find anything else that would push the average above the maximum recorded time. I feel like I am missing something simple.

[Pepe Le Mokko]

I changed tha value in H17 and the average changed to 00:20:47 Are you calculations set to automatic?

[Pete_UK]

Put the call types in cells D314:D316, i.e.:

RTA call
RTA pre admin
RTA post admin

Then you can use this formula in F314:

=AVERAGEIF($B$2:$B$308,$D314,$F$2:$F$308)

Format as Time and copy this down to F316. It will give you the averages directly from the data in column F, so you won't need to bother with separating out the different call types in columns H to J. You can also use AVERAGEIFS, but that has a slightly different syntax, i.e. in G314:

=AVERAGEIFS($F$2:$F$308,$B$2:$B$308,$D314)

It gives exactly the same result as AVERAGEIF, as you would expect.

If you really DO need to get the data into separate columns, you could use formulae to do that, rather than manual copy/pasting.

Hope this helps.

Pete

[Eazygeezer]

Hi,

thank you for the reply. When I copied the cell across from the sample sheet on here back to my master it was still showing 2:11:33. I changed the calculation options to automatic except data tables and reselected the data I was still getting the 2:11;33 result. In the end I finally managed to get it working by copying both the data cells and the calculation from the edited version to my master sheet. There must have been something going on with my data set in the original.

Many thanks for your invaluable assistance

[Eazygeezer]

Hi Pete,

Thanks for that, I am sure it will come in handy and avoid having to copy and paste data around. I tried exactly what you said with the same cell references and everything but I get a DIV/0! error
D313
RTA call #DIV/0! (formula: =AVERAGEIF($B$2:$B$308,$D314,$F$2:$F$308)
RTA pre admin
RTA post admin

I have changed the cell format to time so not sure what is going on here. Sorry about this clearly I know very little about excel

[Croweater]

HI,

Thanks for the spot, yes somehow I had missed that H17 value. However even after I have changed it from 19:36:00 to 00:36:00 the average hours still works out at 02:11:33, I just can't see how this is possible when there are no other figures above 01:30:00 and the majority are in fact below 00:30:00.

The other two average calculations appear to work fine, I have scoured column H looking for other anomalies but I can't find anything else that would push the average above the maximum recorded time. I feel like I am missing something simple.

Look at the DATE.

[Pete_UK]

There is actually a space after RTA Call in all the entries in column B, so you will have to add that in (or you can copy an entry from column B into D314, which is what I did and so did not encounter the error).

Incidentally, if you put this formula in C314:

=COUNTIF($B$2:$B$308,$D314)

and copy down, you will see that there are only 99 entries for the Post Admin calls, but 104 for the other two types, so you must have made some up in column H.

Do you want me to show you how you could generate the data in column H to J using formulae?

Pete

[Eazygeezer]

Hi Pete,

That would be great yes please.

Unfortunately the data recorded by my teams is not as accurate as I would like (hence the inconsistencies in totals) and some of the team have not recording all the admin.
The blanks were values below 10 minutes which are either no contact or rebooked RTA calls and as such would not be included in the average for this data and so I deleted them.

The admin can be any length and so short times don't really have an impact but are useful for me to time the total time taken for a particular job (i.e. RTA call.

Being able to filter those out and calculate from the data in the time column would be the best option but I wasn't sure that was possible.

[Pete_UK]

I've set this up for you in the attached file, with all my additions shown in blue. You can see the block of averages in rows 314-316, as already discussed.

In addition, I've used column G as a helper column with this formula in G2:

=B2&COUNTIF(B$2:B2,B2)

This is copied down to the bottom of your data, and it identifies each call type and tags on a sequential number to the end for each type. I've left your columns H to J the same, for comparison, and copied the 3 call types to L1:N1, then used this formula in L2:

=IFERROR(INDEX($F:$F,MATCH(L$1&ROWS($1:1),$G:$G,0)),"")

This can then be copied across to N2, and then those formulae copied down until you start to get blanks. I've also added conditional formatting to these cells to show yellow if there is a difference of more than 1% between the corresponding times in your columns H to J. Averages at the bottom of these columns are also shown in rows 314 et seq., and not surprisingly the differing methods of calculation all agree with one another.

Hope this helps.

Pete

By the way, if that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete