EXCEL PROBLEM

  broggs 12:22 28 Jun 05
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 05

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

  VoG II 12:56 28 Jun 05

No forget that...

  VoG II 13:04 28 Jun 05

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

  broggs 13:21 28 Jun 05

thanks I'll try that

  broggs 14:14 28 Jun 05

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 05

In any other cell enter

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

  VoG II 17:25 28 Jun 05

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 05

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 05

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.

Surface Pro 5 News - release date, UK price, features, specs

Gear VR vs. Daydream: Which delivers the best VR experience?

iPad Pro 12.9 vs Surface Pro 5