Formula in Excel is different to Access

[mahmed1]

Hi - I am trying to get a total of Labor time and divide by Total chats to give me AHT

The formula in Access returns different to Excel (Maybe its to do with Access times going over 24 hours) not sure

Here is my formula

ROUND((SUM(t.[LABOR TIME])/SUM(t.[INTERACTIVE CHATS]))*86400,0) AS Concurrent_AHT (Average Handling Time)

If i do the same formula in Excel where i take the (sum(Labor Time)/Sum(Interactive Chats))*86400 I am getting different results by a few hundred seconds

Is this a know issue or do i need to calculate the sum of hours differently?

 

[mahmed1]

So in Labour time say i had

14:00
14:00
14:00
14:00

Thats 56:00:00 hours total labor time
Dividing that Total chats = AHT * 86000 i convert it to AHT in seconds
but this does not give the same result of i added in Excel and performed same calculations

 

[xenou]

• you are dealing with a lot of fractional/decimal/floating point values which is always bad for precision in any software
• I think round is different in Access and Excel
• not sure what your datatypes are in Access vs. Excel
• in post 1 you have 86400 and in post 2 you have 86000 so be sure you are using the same factor
• make sure in both cases you are applying rounding in the same way, at the same step in the calculations, and results are formatted the same way (or preferably are not formatted. For comparison might want to not round at all at any point to check results.

 

[mahmed1]

Thank you Xenou - the 86000 was a typo error
It is 86400...

so all im trying to do is sum the total of Chat time which has Hours Mins and Secs (ie 08:15:26) etc.. and then divide that by total labor time which is in the same format and the either round to 2 decimal or even format 2 decimal points....

if i extract the same data from
Access to excel and do the exact same thing sum(chat time)/sum(labor time) i would expect same result but

the *86400 is just to convert result into total seconds

what is the best formula/SQL code/Query in Access to get same result

 

[mahmed1]

• you are dealing with a lot of fractional/decimal/floating point values which is always bad for precision in any software
• I think round is different in Access and Excel
• not sure what your datatypes are in Access vs. Excel
• in post 1 you have 86400 and in post 2 you have 86000 so be sure you are using the same factor
• make sure in both cases you are applying rounding in the same way, at the same step in the calculations, and results are formatted the same way (or preferably are not formatted. For comparison might want to not round at all at any point to check results.

Sorry to use your reply message when responding back.. thank you

 

[xenou]

I don't have a ready at hand formula. Personally I don't like to do math on times or try to divide times by times. I would convert the time to seconds and do all the work in seconds. If you aren't getting correct results I would suggest you post a sample set of data that illustrates the problem.

 

[mahmed1]

Thank you - Im trying to convert all in seconds and then divide
my Access formula i tried was
(Sum(ChatTime)*86400)/(sum(laborTime)*86400)

 

[xenou]

I tried that formula and it works correctly for me.