Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
I need help with a formula. I need to perform a succession of calculations
for a monthly work summary and can't figure out how to write the formula or how to format the cells. I am going to describe how we do this manually: Downtime minutes (410) divided by 60 = DT hours (6.5). DT hours subtracted from Total Hours Worked (80)= Actual Hours Worked (73.5). AHW divided by 8 = Actual Days Worked (9.18). Total Minutes Transcribed (885:40) divided by ADW = Average Minutes Transcribed per day(97.08 roughly), realizing that a calculator cannot calculate minutes and seconds accurately. There is a cell for Total Downtime Minutes, Total Hours Worked, Total Minutes Transcribed, Total Days Worked, and Average Minutes Transcribed. I would be grateful for any help at all. Thanks so much in advance. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
I would convert everything to fractions of a day like excel does.
this code will convert your hours to excel time format 'Time(hours,minutes,seconds) Down Time =TIME(INT(410),MOD(410,60),0) 'Put 80 hours in as days total Hours =80/24 Actual Hours = Total Hours - Down time Actual Days Worked = Actual Hour /(8/24) Total Minutes = Actual Hours/ 60 I don't know where the transcribed number comes from. "Ladyrose" wrote: I need help with a formula. I need to perform a succession of calculations for a monthly work summary and can't figure out how to write the formula or how to format the cells. I am going to describe how we do this manually: Downtime minutes (410) divided by 60 = DT hours (6.5). DT hours subtracted from Total Hours Worked (80)= Actual Hours Worked (73.5). AHW divided by 8 = Actual Days Worked (9.18). Total Minutes Transcribed (885:40) divided by ADW = Average Minutes Transcribed per day(97.08 roughly), realizing that a calculator cannot calculate minutes and seconds accurately. There is a cell for Total Downtime Minutes, Total Hours Worked, Total Minutes Transcribed, Total Days Worked, and Average Minutes Transcribed. I would be grateful for any help at all. Thanks so much in advance. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
Hi,
I'm not too sure about the maths (410/60=6.83 not 6.5) but try this in Colimns A & B starting in Row 1. The formula that gives 96.81 is =B3/((B1-(B2/60))/8) Worked hrs 80 Downtime (M) 410 Tot mins transcribed 885.4 Average mis transcribed 96.81 Mike "Ladyrose" wrote: I need help with a formula. I need to perform a succession of calculations for a monthly work summary and can't figure out how to write the formula or how to format the cells. I am going to describe how we do this manually: Downtime minutes (410) divided by 60 = DT hours (6.5). DT hours subtracted from Total Hours Worked (80)= Actual Hours Worked (73.5). AHW divided by 8 = Actual Days Worked (9.18). Total Minutes Transcribed (885:40) divided by ADW = Average Minutes Transcribed per day(97.08 roughly), realizing that a calculator cannot calculate minutes and seconds accurately. There is a cell for Total Downtime Minutes, Total Hours Worked, Total Minutes Transcribed, Total Days Worked, and Average Minutes Transcribed. I would be grateful for any help at all. Thanks so much in advance. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
"Joel" wrote: I would convert everything to fractions of a day like excel does. this code will convert your hours to excel time format 'Time(hours,minutes,seconds) Down Time =TIME(INT(410),MOD(410,60),0) 'Put 80 hours in as days total Hours =80/24 Actual Hours = Total Hours - Down time Actual Days Worked = Actual Hour /(8/24) Total Minutes = Actual Hours/ 60 I don't know where the transcribed number comes from. "Ladyrose" wrote: I need help with a formula. I need to perform a succession of calculations for a monthly work summary and can't figure out how to write the formula or how to format the cells. I am going to describe how we do this manually: Downtime minutes (410) divided by 60 = DT hours (6.5). DT hours subtracted from Total Hours Worked (80)= Actual Hours Worked (73.5). AHW divided by 8 = Actual Days Worked (9.18). Total Minutes Transcribed (885:40) divided by ADW = Average Minutes Transcribed per day(97.08 roughly), realizing that a calculator cannot calculate minutes and seconds accurately. There is a cell for Total Downtime Minutes, Total Hours Worked, Total Minutes Transcribed, Total Days Worked, and Average Minutes Transcribed. I would be grateful for any help at all. Thanks so much in advance. Thanks for the reply Joel, but I guess my explanation of the process was not precise enough. This is a MONTHLY spreadsheet, with a daily tally from day 1 of the month to day 31 in columns labeled Total Days Worked, Total Minutes Transcribed, Total Hours Worked and Total Downtime Minutes with their respective =SUM formula at the end of each column, except for Days Worked which is a =COUNT formula. We use the latter three monthly totals to perform the calculation I described above. TDW total is in cell B35, TMW total is in cell C35, THW in cell D35 and TDM in cell M35. Average Minutes Transcribed has a cell assignment of C37, and obviously is without a formula. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
Thanks for the reply Mike, but I guess my explanation of the process was not
precise enough. This is a MONTHLY spreadsheet, with a daily tally from day 1 of the month to day 31 in columns labeled Total Days Worked, Total Minutes Transcribed, Total Hours Worked and Total Downtime Minutes with their respective =SUM formula at the end of each column, except for Days Worked which is a =COUNT formula. We use the latter three monthly totals to perform the calculation I described above. TDW total is in cell B35, TMW total is in cell C35, THW in cell D35 and TDM in cell M35. Average Minutes Transcribed has a cell assignment of C37, and obviously is without a formula. "Mike H" wrote: Hi, I'm not too sure about the maths (410/60=6.83 not 6.5) but try this in Colimns A & B starting in Row 1. The formula that gives 96.81 is =B3/((B1-(B2/60))/8) Worked hrs 80 Downtime (M) 410 Tot mins transcribed 885.4 Average mis transcribed 96.81 Mike "Ladyrose" wrote: I need help with a formula. I need to perform a succession of calculations for a monthly work summary and can't figure out how to write the formula or how to format the cells. I am going to describe how we do this manually: Downtime minutes (410) divided by 60 = DT hours (6.5). DT hours subtracted from Total Hours Worked (80)= Actual Hours Worked (73.5). AHW divided by 8 = Actual Days Worked (9.18). Total Minutes Transcribed (885:40) divided by ADW = Average Minutes Transcribed per day(97.08 roughly), realizing that a calculator cannot calculate minutes and seconds accurately. There is a cell for Total Downtime Minutes, Total Hours Worked, Total Minutes Transcribed, Total Days Worked, and Average Minutes Transcribed. I would be grateful for any help at all. Thanks so much in advance. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel formula
Thanks for the reply Joel, but I guess my explanation of the process was not
precise enough. This is a MONTHLY spreadsheet, with a daily tally from day 1 of the month to day 31 in columns labeled Total Days Worked, Total Minutes Transcribed, Total Hours Worked and Total Downtime Minutes with their respective =SUM formula at the end of each column, except for Days Worked which is a =COUNT formula. We use the latter three monthly totals to perform the calculation I described above. TDW total is in cell B35, TMW total is in cell C35, THW in cell D35 and TDM in cell M35. Average Minutes Transcribed has a cell assignment of C37, and obviously is without a formula. "Joel" wrote: I would convert everything to fractions of a day like excel does. this code will convert your hours to excel time format 'Time(hours,minutes,seconds) Down Time =TIME(INT(410),MOD(410,60),0) 'Put 80 hours in as days total Hours =80/24 Actual Hours = Total Hours - Down time Actual Days Worked = Actual Hour /(8/24) Total Minutes = Actual Hours/ 60 I don't know where the transcribed number comes from. "Ladyrose" wrote: I need help with a formula. I need to perform a succession of calculations for a monthly work summary and can't figure out how to write the formula or how to format the cells. I am going to describe how we do this manually: Downtime minutes (410) divided by 60 = DT hours (6.5). DT hours subtracted from Total Hours Worked (80)= Actual Hours Worked (73.5). AHW divided by 8 = Actual Days Worked (9.18). Total Minutes Transcribed (885:40) divided by ADW = Average Minutes Transcribed per day(97.08 roughly), realizing that a calculator cannot calculate minutes and seconds accurately. There is a cell for Total Downtime Minutes, Total Hours Worked, Total Minutes Transcribed, Total Days Worked, and Average Minutes Transcribed. I would be grateful for any help at all. Thanks so much in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When I enter a formula, Excel shows the formula not the results | Excel Worksheet Functions | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
How do I view formula results intead of formula in excel? | Excel Worksheet Functions |