EXCEL PROBLEM

  broggs 12:22 28 Jun 2005
Locked

Hello....I have a column in excel which I want to find the average of.The data in the rows are times in hours and minutes e.g 2.15, 3.45, 4.43 etc.How do I do this without them being treated as decimals and not hours and minutes??
thanks

  VoG II 12:55 28 Jun 2005

=(INT(A1)+INT(A2)+INT(A3)+MOD(A1,1)+MOD(A2,1)+MOD(A3,1))/3

  VoG II 12:56 28 Jun 2005

No forget that...

  VoG II 13:04 28 Jun 2005

=INT(AVERAGE(A1:A3)) + MOD(AVERAGE(A1:A3),1)*0.6

  broggs 13:21 28 Jun 2005

thanks I'll try that

  broggs 14:14 28 Jun 2005

sorry VOG....could you tell me how to use this formula.
the heading is in A1 then below it are the times going down to A74
thanks

  VoG II 15:25 28 Jun 2005

In any other cell enter

=INT(AVERAGE(A2:A74)) + MOD(AVERAGE(A2:A74),1)*0.6

  VoG II 17:25 28 Jun 2005

Sorry, now I've had more time to think about this, it doesn't work.

The easiest wat to do what you want is to convert all of your times expressed like 2.45 into Excel times 2:45.

To do this select A2:A74, Edit | Replace. In the Find What box enter . and in the Replace with box enter : then click Replace All.

In any other cell the following will give you the average:

=AVERAGE(A2:A74)

(You may need to format this cell as time if Excel doen't do that automatically)

  broggs 20:37 28 Jun 2005

thanks VOG.....the time in the cells already have : between the figures.My fault.Will possibly not need to use the replace calc then.s using the formula seems to have worked though so will tick as resolved.Once again thanks for your time and input VOG

  broggs 20:54 28 Jun 2005

The replace command does need to be used to make the calculation work
cheers....a nice cold beer awaits thanks

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

The Evil Within 2 review-in-progress

Photoshop CC 2018 released with new Curvature Pen and better brush tools

Camera tips to take better iPhone photos

Les meilleures applications de covoiturage 2017