Trying (and failing) to calculate average time in hours by name by priority

[Mendonk]

Hi, I have been trying to sort this out for weeks and I'm hoping someone can help me. I think the root cause of some of it is simply that Excel doesn't like working out difference between dd/mm/yyyy hh:mm:ss formats so I have to use the 1904 setting (which causes further problems elsewhere) but what I need it to understand in my team how long (in hours) on average it is taking them to attend (or complete) tasks by priority. I've attached an example where I have manually typed in the correct values (yellow cells) the answers are a bit rubbish because there isn't much data where in the actual spreadie there are thousands of lines. I've tried doing it as a pivot but that doesn't like the 1904 business and seems to be throwing out all sorts of inaccurate numbers but in essence I need to know 'how many hours on average does it take Bill to attend a P1 job'

If anyone can help I'd be beyond grateful. We're three months into the year and I still haven't managed to give my team any accurate performance data!

Thanks

Kt

[Richard Buttrey]

Is that a typo for John - P1 since there isn't any data for that combination?

You mention 1904 settings implying you're using Excel for Mac. If so would you update your profile so that it's made clear. Personally I don't see why that would make a difference here.

Do you have the AVERAGEIFS function?

[etaf]

working on the spreadsheet at the same time Richard Buttrey posted
I had the same ISSUE with the example , not matching the data
ie John P1

and used a AverageIFS , and do not get anything like the result
I also reworked the Time taken using a formula , rather than a date

see green block

=AVERAGEIFS($D$2:$D$16,$F$2:$F$16,J$2,$G$2:$G$16,$I3)

left the error in but can be easily fixed

I'm on a mac and use the 1900 date NOT the 1904 as a 1 returns 1/1/1900

Is that office version 2010 in profile

[Richard Buttrey]

Hi,

Try

=IFERROR(AVERAGEIFS($E$2:$E$16,$G$2:$G$16,$I11,$F$2:$F$16,J$10)*24,"")

In I11 copied across and down

[Mendonk]

No I'm on a windows PC not a Mac. I had to use the 1904 thing to fix another formula in the spreadsheet where it wouldn't calculate negative times. For what is supposed to be a really simple thing to be able to do this spreadie seems to have become a world of pain!

[Richard Buttrey]

Excel doesn't like negative times. These normally arise when an end time in a set of data is earlier than a start time on a previous day. In that case you need to trap that situation and extend a time difference formula by adding 24.

Have you tried the #4 suggestion?

[Mendonk]

Yes #4 has worked thank you Richard. And the Excel guru from work has also been on and fixed it all as well.

Thanks everyone!

Kt