Formula Help for Allocation % of work week based on employment type

KatieS3941

Hi - I'm trying to figure out if I can put an automated column into Smartsheet. I think that I might need to use a couple formulas, maybe in multiple columns, but for the life of me I just can't figure out how to make it work. My manager asked me to list out percent of workweek allocation, based on different work types. I think I could connect it to their name if they enter a number of how long each task takes, but I've hit a wall.

For example, if Wanda puts in that she will need to spend 4 hours on Task A, 10% should fill into the next column automatically. Then if she's estimating (or has worked) 2 hours on Task B, 5% should populate into the new column. You can see at the bottom that Wanda is Full-time and works 40 hours per week, whereas William works ten hours per week.

Thanks!

KPH

The column that you want to populate is the number of hours worked or estimated (as entered by the employee) divided by the number of hours they work per week, in a column set as a percent format.

To do this, you need something on the row that provides both parts of the calculation.

(1) Hours worked/estimated - you say this will be entered in a column.

(2) Another column is needed to identify the denominator in the calculation. This could also be entered by the employee - hours per week. Or it could be something else such as the employee name, or employee type (FT, PT, CA, CB), with another sheet that includes each name or type and the hours they work.

Then, in the column that you want the percentage to appear you can divide (1) by the number hours, which will either be in column (2) or found in another sheet using the information in column (2) as a look-up.

In a simple form, I think this is what you are aiming for:

Data is entered in the white columns, the yellow one has a formula that calculates the percentage.

If you don't want hours per week to be entered, you could ask for employee name or type and use an INDEX MATCH formula to look up the number of hours from another sheet. I would use a drop-down list to ensure users enter a consistent name or employee type.

You then need a look-up sheet like this:

In your main sheet the employee can enter the task, hours and then choose their employee type from a list that matches the list in the look-up sheet:

The formula in the yellow column, now uses an INDEX MATCH formula to find the hours per week from the look-up sheet. It will look something like this:

=Hours@row / (INDEX({Hours per week}, MATCH([Employee type]@row, {Employee type}, 0)))

Because the look-up table is in a separate sheet you will need to use cross sheet references. If you are new to those, take a look at: https://help.smartsheet.com/learning-track/level-3-advanced-users/cross-sheet-formulas

Here are the ones I used:

I hope that helps.