Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentage problem.........Bernard Liengme
I have to collate monthly hours flown by two units.
I need a break down in percent of each unit useage for the first 30 hours and then again for the remaining hours above 30. A sample below: Columns: A B Unit Hours Police 0:20 Police 0:30 Medical 0:10 Police 0:50 Medical 0:05 medical 0:55 and so on...... Cells: Total hours for the month C2 D2 Police Medical C8 D8 Police Medical Percentage Percentage of hours of hours used used under 30 hours under 30hours. Cells: C10 D10 Percentage Percentage use over use over 30hours 30hours Hope that helps. -- AOU -- AOU |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentage problem.........Bernard Liengme
Thanks Sandy.
I need to know the hours used in percent for each unit in two blocks. The first bloke goes up to 41hours and 41 minutes. The second is anything above that figure. A little more info: Up to 41:40hours Medical hours are budgeted to 30% and above that it is 25%. I need to know when that figure is passed my the Medical unit and by how much (in percent). And the easiest way is to know how much each unit is useing for each bloke. Hope that makes it any clearer! -- AOU "Sandy Mann" wrote: Im not Bernard but: C2 formula: =SUMIF(A2:A7,"Police",B2:B7) D2 formula: =SUMIF(A2:A7,"Medical",B2:B7) I don't know what you mean by "Percentage over/under 30 hours" percentage of what? The totalof all hour? The totalof only the Police ot Medical? or what? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "AOU" wrote in message ... I have to collate monthly hours flown by two units. I need a break down in percent of each unit useage for the first 30 hours and then again for the remaining hours above 30. A sample below: Columns: A B Unit Hours Police 0:20 Police 0:30 Medical 0:10 Police 0:50 Medical 0:05 medical 0:55 and so on...... Cells: Total hours for the month C2 D2 Police Medical C8 D8 Police Medical Percentage Percentage of hours of hours used used under 30 hours under 30hours. Cells: C10 D10 Percentage Percentage use over use over 30hours 30hours Hope that helps. -- AOU -- AOU |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentage problem.........Bernard Liengme
Well, I should have added to my previous post to Custom Format the cells as
"[h]:mm" (without the quotes) to prevent the hours rolling over into days when they get to 24 hours. With the total Police and Medical hours in C2 and D2 respectively as befo Cell C8: Police hours under 41:40 minutes: =MIN((41+2/3)/24,C2) Cell D8: Medical hours under 41:40 minutes: =MIN((41+2/3)/24,D2) Both formatted as above. If the total hours for Police or Medical are over 41:40 minutes these cells will show 41:40 (The TIME() function does not work at more then 24 hours so because 1 hour is 1/24th of a day, 41 2/3 divided by 24 converts the 41.66666666 decimal into a time when the cell is formated as [h]:mm) Cell C10: Police hours over 41:40: =MAX(C2-(41+2/3)/24,0) Cell D10: Medical hours over 41:40: =MAX(D2-(41+2/3)/24,0) Assuming that you want the percentages of the total Police + Medical times then: % Police hours: Under 41:40: =C8/(C2+D2) Over 41:40: =C10/(C2+D2) % Medical: Under 41:40: =D8/(C2+D2) Over 41:40: =D10/(C2+D2) All formated as percentage -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "AOU" wrote in message ... Thanks Sandy. I need to know the hours used in percent for each unit in two blocks. The first bloke goes up to 41hours and 41 minutes. The second is anything above that figure. A little more info: Up to 41:40hours Medical hours are budgeted to 30% and above that it is 25%. I need to know when that figure is passed my the Medical unit and by how much (in percent). And the easiest way is to know how much each unit is useing for each bloke. Hope that makes it any clearer! -- AOU "Sandy Mann" wrote: Im not Bernard but: C2 formula: =SUMIF(A2:A7,"Police",B2:B7) D2 formula: =SUMIF(A2:A7,"Medical",B2:B7) I don't know what you mean by "Percentage over/under 30 hours" percentage of what? The totalof all hour? The totalof only the Police ot Medical? or what? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "AOU" wrote in message ... I have to collate monthly hours flown by two units. I need a break down in percent of each unit useage for the first 30 hours and then again for the remaining hours above 30. A sample below: Columns: A B Unit Hours Police 0:20 Police 0:30 Medical 0:10 Police 0:50 Medical 0:05 medical 0:55 and so on...... Cells: Total hours for the month C2 D2 Police Medical C8 D8 Police Medical Percentage Percentage of hours of hours used used under 30 hours under 30hours. Cells: C10 D10 Percentage Percentage use over use over 30hours 30hours Hope that helps. -- AOU -- AOU |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentage problem.........Bernard Liengme
Sandy,
Thank you very much. That was exactly what I was looking for, it works well. -- AOU "Sandy Mann" wrote: Well, I should have added to my previous post to Custom Format the cells as "[h]:mm" (without the quotes) to prevent the hours rolling over into days when they get to 24 hours. With the total Police and Medical hours in C2 and D2 respectively as befo Cell C8: Police hours under 41:40 minutes: =MIN((41+2/3)/24,C2) Cell D8: Medical hours under 41:40 minutes: =MIN((41+2/3)/24,D2) Both formatted as above. If the total hours for Police or Medical are over 41:40 minutes these cells will show 41:40 (The TIME() function does not work at more then 24 hours so because 1 hour is 1/24th of a day, 41 2/3 divided by 24 converts the 41.66666666 decimal into a time when the cell is formated as [h]:mm) Cell C10: Police hours over 41:40: =MAX(C2-(41+2/3)/24,0) Cell D10: Medical hours over 41:40: =MAX(D2-(41+2/3)/24,0) Assuming that you want the percentages of the total Police + Medical times then: % Police hours: Under 41:40: =C8/(C2+D2) Over 41:40: =C10/(C2+D2) % Medical: Under 41:40: =D8/(C2+D2) Over 41:40: =D10/(C2+D2) All formated as percentage -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "AOU" wrote in message ... Thanks Sandy. I need to know the hours used in percent for each unit in two blocks. The first bloke goes up to 41hours and 41 minutes. The second is anything above that figure. A little more info: Up to 41:40hours Medical hours are budgeted to 30% and above that it is 25%. I need to know when that figure is passed my the Medical unit and by how much (in percent). And the easiest way is to know how much each unit is useing for each bloke. Hope that makes it any clearer! -- AOU "Sandy Mann" wrote: Im not Bernard but: C2 formula: =SUMIF(A2:A7,"Police",B2:B7) D2 formula: =SUMIF(A2:A7,"Medical",B2:B7) I don't know what you mean by "Percentage over/under 30 hours" percentage of what? The totalof all hour? The totalof only the Police ot Medical? or what? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "AOU" wrote in message ... I have to collate monthly hours flown by two units. I need a break down in percent of each unit useage for the first 30 hours and then again for the remaining hours above 30. A sample below: Columns: A B Unit Hours Police 0:20 Police 0:30 Medical 0:10 Police 0:50 Medical 0:05 medical 0:55 and so on...... Cells: Total hours for the month C2 D2 Police Medical C8 D8 Police Medical Percentage Percentage of hours of hours used used under 30 hours under 30hours. Cells: C10 D10 Percentage Percentage use over use over 30hours 30hours Hope that helps. -- AOU -- AOU |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Percentage problem.........Bernard Liengme
I'm glad that it worked for you. Thanks for posting back
-- Regards, Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "AOU" wrote in message ... Sandy, Thank you very much. That was exactly what I was looking for, it works well. -- AOU "Sandy Mann" wrote: Well, I should have added to my previous post to Custom Format the cells as "[h]:mm" (without the quotes) to prevent the hours rolling over into days when they get to 24 hours. With the total Police and Medical hours in C2 and D2 respectively as befo Cell C8: Police hours under 41:40 minutes: =MIN((41+2/3)/24,C2) Cell D8: Medical hours under 41:40 minutes: =MIN((41+2/3)/24,D2) Both formatted as above. If the total hours for Police or Medical are over 41:40 minutes these cells will show 41:40 (The TIME() function does not work at more then 24 hours so because 1 hour is 1/24th of a day, 41 2/3 divided by 24 converts the 41.66666666 decimal into a time when the cell is formated as [h]:mm) Cell C10: Police hours over 41:40: =MAX(C2-(41+2/3)/24,0) Cell D10: Medical hours over 41:40: =MAX(D2-(41+2/3)/24,0) Assuming that you want the percentages of the total Police + Medical times then: % Police hours: Under 41:40: =C8/(C2+D2) Over 41:40: =C10/(C2+D2) % Medical: Under 41:40: =D8/(C2+D2) Over 41:40: =D10/(C2+D2) All formated as percentage -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "AOU" wrote in message ... Thanks Sandy. I need to know the hours used in percent for each unit in two blocks. The first bloke goes up to 41hours and 41 minutes. The second is anything above that figure. A little more info: Up to 41:40hours Medical hours are budgeted to 30% and above that it is 25%. I need to know when that figure is passed my the Medical unit and by how much (in percent). And the easiest way is to know how much each unit is useing for each bloke. Hope that makes it any clearer! -- AOU "Sandy Mann" wrote: Im not Bernard but: C2 formula: =SUMIF(A2:A7,"Police",B2:B7) D2 formula: =SUMIF(A2:A7,"Medical",B2:B7) I don't know what you mean by "Percentage over/under 30 hours" percentage of what? The totalof all hour? The totalof only the Police ot Medical? or what? -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "AOU" wrote in message ... I have to collate monthly hours flown by two units. I need a break down in percent of each unit useage for the first 30 hours and then again for the remaining hours above 30. A sample below: Columns: A B Unit Hours Police 0:20 Police 0:30 Medical 0:10 Police 0:50 Medical 0:05 medical 0:55 and so on...... Cells: Total hours for the month C2 D2 Police Medical C8 D8 Police Medical Percentage Percentage of hours of hours used used under 30 hours under 30hours. Cells: C10 D10 Percentage Percentage use over use over 30hours 30hours Hope that helps. -- AOU -- AOU |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percentage problem | Excel Discussion (Misc queries) | |||
Thank You Bernard | Excel Worksheet Functions | |||
To Mr. Liengme: Re My previous Post Concerning My Bar Chart Problem | Charts and Charting in Excel | |||
Help Please with a percentage problem | New Users to Excel | |||
To Bernard or anyone who can help | Excel Discussion (Misc queries) |