Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM or AVERAGE of mm:ss
I need help, I received link references from a corporate spreadsheet with
time format 13:30 (representing mm:ss). I am trying to get at SUM and AVERAGE of these cells and having a hard time getting it right. I did try to have output format as [m]:ss and it is coming out incorrectly. If I have to convert these to h:mm:ss is there a way to make to conversion happen automatically. Example: 20:12 5:31 9:47 8:45 8:27 7:25 20:21 17:46 13:01 22:17 SUM these to format [m]:ss will give me 15212:00 SUM these to format mm:mm will give me 32:00 AVERAGE these to format 13:30 will give me 1:21 Can someone explain what is Excel doing here and possible offer me a solution for AVERAGE these times. Thanks, Kha |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM or AVERAGE of mm:ss
Although these may look like minutes and seconds, I think Excel has
taken them as hours, minutes and seconds and is just displaying [h]:mm. Click on one of the cells and check the format with Format | Cells | Number (tab). If this is the case, then in a blank column enter the formula: =A1/60, assuming your times are in column A, and format as [m]:ss. Copy this down. You can fix the values using <copy with Edit | Paste Special | Values | OK, and you can also paste the fixed values to over-write the original values. Hopefully, your SUM and AVERAGE formulae will work after this. Hope this helps. Pete |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM or AVERAGE of mm:ss
The cell format is actually Time13:30
"Pete" wrote: Although these may look like minutes and seconds, I think Excel has taken them as hours, minutes and seconds and is just displaying [h]:mm. Click on one of the cells and check the format with Format | Cells | Number (tab). If this is the case, then in a blank column enter the formula: =A1/60, assuming your times are in column A, and format as [m]:ss. Copy this down. You can fix the values using <copy with Edit | Paste Special | Values | OK, and you can also paste the fixed values to over-write the original values. Hopefully, your SUM and AVERAGE formulae will work after this. Hope this helps. Pete |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM or AVERAGE of mm:ss
Yes, if you do Format | Cells | Number (tab) then it shows Time with
13:30 - this means hours and minutes. Click on 13:30:55 (two down on the list) and you should see your first value change to 20:12:00, in which case it is formatted as I suggested and you can follow my suggestions to rectify it. Pete |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM or AVERAGE of mm:ss
Now, I am really in trouble because I want them the be in mm:ss not hh:mm:ss
i.e. the first number should be 0:20:12. Can you help with this? or another forum is needed here. "Pete" wrote: Yes, if you do Format | Cells | Number (tab) then it shows Time with 13:30 - this means hours and minutes. Click on 13:30:55 (two down on the list) and you should see your first value change to 20:12:00, in which case it is formatted as I suggested and you can follow my suggestions to rectify it. Pete |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUM or AVERAGE of mm:ss
Read the second part of my first posting, ie:
" ... If this is the case, then in a blank column enter the formula: =A1/60, assuming your times are in column A, and format as [m]:ss. Copy this down. You can fix the values using <copy with Edit | Paste Special | Values | OK, and you can also paste the fixed values to over-write the original values. Hopefully, your SUM and AVERAGE formulae will work after this. Hope this helps. ...." And I hope this re-posting helps. Pete |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for determing average based on weighting | Excel Worksheet Functions | |||
plotted Average | Charts and Charting in Excel | |||
What is this kind of average called? | Excel Worksheet Functions | |||
Average Formula with Criteria | Excel Discussion (Misc queries) | |||
average function in Excel 2002 | New Users to Excel |