Selecting the best excel formula for calculating desk allocation within a building?

[alan1988]

Hi, I am wondering if anyone could help me in selecting the correct formula for allocating desks within a specific building, based on the total headcount in a team, the full time equivelant of staff in a team and the worker profile ratios below:

Total headcount in team = 10
Full time equivelant in team = 8.0

Fixed staff = 5 staff (desk allocation is 1:1 - 1 desk to 1 member of staff)
Flexible staff = 3 staff (desk allocation is 1 desk to 2 members of staff)
Mobile staff = 2 staff (desk allocation is 1 desk to 5 members of staff)

All staff are going to be located at Building A

So based on the data above, I would like to work out the desk allocation for Building A but cannot figure out a fomula(s) that would auto calculate this for me!

Any help would be appreciated
Thanks

[loginjmor]

Hi -

Wouldn't it be 1xNumberOfFixedStaff + 0.67xNumberOfFlexStaff + 0.2xNumberOfMobileStaff Rounded up to the next highest number?

So, for example if Cells A3, B3, and C3 are the number of Fixed Staff, Flexible Staff, and Mobile Staff, respectively, the formula in say Cell D3 would look like:

=ROUNDUP(A3*1+B3*0.67+C3*0.2,0)

For the numbers given in your example, the formula would yield 8 desks.

Hope this helps.