formula

[cocacola]

hi and thanks!

I am needing a formula to reflect a percentage as follows:

i am calculating number of hours on the phone (i.e.: 8:30, (8 1/2 hours) minus number of hours off the phone (i.e. 1:45, (1 hour, 45 minutes). Then i need to show a percentage of time on the phone. How can I get a percentage when my format is in [h]:mm?

thx!

 

[Dave Patton]

On 2002-10-28 16:57, cocacola wrote:
hi and thanks!

I am needing a formula to reflect a percentage as follows:

i am calculating number of hours on the phone (i.e.: 8:30, (8 1/2 hours) minus number of hours off the phone (i.e. 1:45, (1 hour, 45 minutes). Then i need to show a percentage of time on the phone. How can I get a percentage when my format is in [h]:mm?

thx!

Format your result cell as %

example with your numbers in A2 and A3

=(A3/A2) without formatting gives 0.205882353

or 20.59%

 

[cocacola]

it's actually like this:

=sum(L9-L28-L30/160)

and i'm not getting the same number as if i were to do the math on the calculator:

the first cell reference is a total number of hours worked, the second subtracting leave taken, the third is time off the phone, then that total is divided by the number of hours scheduled to be worked in the month (160). the answer s/b 45% or there abouts. under the 'number format' i'm getting 3.76 as an answer, under the 'percentage' format, i get #####.

L9=92:21
L28=2:00
L30=18:40

L9 is the total hours (92 hours, 21 minutes), minus total leave (2 hours), minus total off-line hours (18 hours, 40 minutes), divided by 160 hours for a percentage of approximately 44%.

???

 

[plettieri]

Hi

You probably have to convert your time cells to whole numbers to get a percentage you are looking for.....a good thread and a better understanding of this this works in http://www.mvps.org/dmcritchie/excel/datetime.htm

hope this helps

pll

 

[IML]

A couple things will fix this right up:
1) You don't need the sum, it redundant since you have the operaters in there.
That would be
=L9-L28-L30/160
2) This would first divide L30 by 160 and subtract this and L28 from L9 - not what you want. Add parenthesis like this:
=(L9-L28-L30)/160
3) Excel reads time as fraction of a day, so 160 = 160 days or 3,840 hours. Change this by dividing your number by 24 to turn it into consistent hour. End result:
=(L9-L28-L30)/(160/24)
which equates to about 44.8%.
This message was edited by IML on 2002-10-28 17:32

 

[RichardS]

=(L9-L10-L11)/L13

Where L13 contains 160:00. Format result as %

Richard

 

[cocacola]

continuing journey...

thanks for your help!

question: when i copy a worksheet (entire page) to paste into a new workbook, my formulas are not coming over when I paste. why come?