Hello, I am looking for some kind of formula to quickly calculate percentage of Overtime to provide in a report to our General Manager. I run a report that gives me total hours in different categories but it would be nice if I could make some sort of template that I could simply paste those hours into and have a formula that would auto-calculate those hours. Hour types are pasted in A1 from the report
The formula would be in B13 and I am looking for the following:
Only sum the cells that have Day and Nite Reg hours, Day and Nite OT hours, and Day and Nite Doub hours. Then divide the sum of the OT and Doub hours(both day and nite of course) by that previous total.
When I calculated this out manually, I had gotten 33% OT if that will help test any formula.
Is there a formula that can encompass all of this with me simply pasting from the report I get it off of and without me having to rearrange cells?
Day Reg: | 2131.3 | Day OT: | 1064.2 | Nite Reg: | 2774.3 |
Nite OT: | 606.2 | DAY HOL: | 744 | DAY DOUB: | 423.7 |
NITEDOUB: | 282.6 | DAY VAC: | 248 | NITE VAC: | 696 |
DAY SS: | 96 | DAY PD: | 50 | DAY BD: | 10 |
MED LV: | 0.48 | ACC-SCK: | 3.94 | Wrk-Comp: | 1.44 |
SH DVAC: | 20 | SH NVAC: | 37 | SP DVAC: | 36 |
SP NVAC: | 8 | Vacation: | 2.8 | Excused: | 0.41 |
Unexcuse: | 0.62 | NITE HOL: | 1048 | NITE SS: | 124 |
NITE PD: | 66 | NITE BD: | 32 | TOTAL: | 10497.3 |
Total % of OT |
The formula would be in B13 and I am looking for the following:
Only sum the cells that have Day and Nite Reg hours, Day and Nite OT hours, and Day and Nite Doub hours. Then divide the sum of the OT and Doub hours(both day and nite of course) by that previous total.
When I calculated this out manually, I had gotten 33% OT if that will help test any formula.
Is there a formula that can encompass all of this with me simply pasting from the report I get it off of and without me having to rearrange cells?