formula

cocacola

New Member
Joined
Oct 16, 2002
Messages
26
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!
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,971
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Oct 16, 2002
Messages
26
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%.

???
 

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743

ADVERTISEMENT

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

Well-known Member
Joined
Feb 16, 2002
Messages
761
=(L9-L10-L11)/L13

Where L13 contains 160:00. Format result as %

Richard
 

cocacola

New Member
Joined
Oct 16, 2002
Messages
26
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?
 

Forum statistics

Threads
1,144,329
Messages
5,723,736
Members
422,512
Latest member
MHau5

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top