Excel formula

[Ladyrose]

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.

 

[Joel]

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.

 

[Mike H]

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]

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.

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.

 

[Ladyrose]

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.

 

[Ladyrose]

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.