Calculate an employee's utilization over a quarter

[SuperRed2]

We record each employee's billable time each week with the goal of making sure they are at least 75% utilized each week. In a row, we have the employees name and the hours they worked that week. The goal is to be able to see their total utilization as the weeks tick by. Attached is what the spreadsheet looks like, I'm just at a loss on how to figure each week and not calculate weeks (cells that are empty).

As you can see, the formula that I am currently using, doesn't calculate correctly. What I would like to do is somehow take the possible hours that are listed at the top and divide them by the actual worked hours in Bob's row. But then how do I not include weeks that are not completed yet? thanks for any help you can provide...

Dave

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future, you can attract more helpers by providing sample data that can easily be copied for testing. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if something like this would be any use once adapted to your layout.

23 01 30.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P
9	32		40		32		40		32
10
11	FF	SA	FF	SA	FF	SA	FF	SA	FF	SA		Total SA	%
12	20			32	3	36				32		100	96.2%
13		32						40				72	100.0%
14				0								0	0.0%
Utilization

Cell Formulas
Range	Formula
O12:O14	O12	=SUMIF(D$11:M$11,"SA",D12:M12)
P12:P14	P12	=O12/SUMPRODUCT(D$9:L$9,--(E$11:M$11="SA"),--(E12:M12<>""))

 

[SuperRed2]

Peter,

That is awesome, thank you so much! And also thank you for the helpful hints on how to post in the future. I do have one more question, When I have a resource that has both SA and FF time entries, the current formula only shows % billable for one or the other, not both. I would like to calculate all the hours in that row regardless if they are FF or SA hours for a total % billable in P12. How would I adjust the current formula =O12/SUMPRODUCT(D$9:L$9,--(E$11:M$11="SA"),--(E12:M12<>"")) to calculate both? It may be that I have to also change the formula in O12 as well. Thanks again for the help, this has been great!

 

[Peter_SSs]

Could you give some small sample data with the expected results (calculated manually) entered with XL2BB?
And then explain again in relation to that sample data?

Or even tell me what the expected results would be for rows 12 & 13 in my sample data above (row 14 will obviously still be 0%) and how you calculated those results manually.

 

[SuperRed2]

I can't get XL2BB to work so I'll just explain. Row 12 would be the hours that Bob Smith worked each week. He may work on a fixed fee project (FF) or work in a Staff Aug capacity (SA) or sometimes both. I need to add those hours together for a total amount of hours for each week of the quarter. In your example, row 9 is the available hours each week so most weeks would be 40 hours. Row 11 would indicate whether they were FF or SA hours and then row 12 would be the actual hours Bob worked that week. As we progress through the quarter, we would like to see his % billable. In your example, cell P12 would be that utilization percentage. In theory, if Bob worked 40 hours the first three weeks of quarter 1 and there were 40 available hours, his % billable would be 100%.

I hope that helps explain a little more clearly.
Dave

 

[Peter_SSs]

I can't get XL2BB to work

What are the symptoms? What goes wrong and at what point in the instruction? Mostly we can resolve the issues.

In your example, cell P12 would be that utilization percentage.

What utilization percentage?

In my sample exactly what number would appear in each of cells P12 and P13 and how do you calculate that number? For the how, I am looking for specifics like this made-up example:
P12 would be 90.44%
I got that by summing D12:M12 (123) and dividing by 136 (adding all the numbers in row 9 where there are values in D12:M12) and writing that as a percentage.

 

[SuperRed2]

In your sample, P12 the quarterly billable % for Bob, and P13 is the quarterly percentage for another employee, Barbara. Each row represents an employee so row 12 would be Bobs weekly hours manually entered, and row 13 would be Barbaras weekly hours manually entered. If you look at my example at the top, that is what our sheet looks like just for Bob.

 

[SuperRed2]

XL2BB is installed and I disabled macros, but there are no options when I click on the XL2BB tab. It looks like this...

 

[Peter_SSs]

In your sample, P12 the quarterly billable % for Bob, and P13 is the quarterly percentage for another employee, Barbara. Each row represents an employee so row 12 would be Bobs weekly hours manually entered, and row 13 would be Barbaras weekly hours manually entered. If you look at my example at the top, that is what our sheet looks like just for Bob.

I'm afraid that none of that addresses anything that I asked:

In my sample exactly what number would appear in each of cells P12 and P13 and how do you calculate that number? For the how, I am looking for specifics like this made-up example:
P12 would be 90.44%
I got that by summing D12:M12 (123) and dividing by 136 (adding all the numbers in row 9 where there are values in D12:M12) and writing that as a percentage.

XL2BB is installed and I disabled macros,

Do mean that you enabled macros?
Have a look here:
Xl2bb is disabled