Need formula to establish how many sales managers I need

[Rdavis]

I am hiring 5-10 new sales people per week. I want to pair 1 sales manager to each 15 sales people hired. So, in week 1, I have zero sales people, but I need 1 sales manager. So, if I begin my spreadsheet with 1 sales manager, then add 5 sales people, then another 5 the following week, then another 5 the following week... by the 4th week, I will need a 2nd sales manager. If I increase my recruitment efforts...and now am hiring 10-15, etc., sales people per week... I need my spreadsheet to continually outline how many sales managers I will need. How do I write that command? Thank you to all who contribute here. I suspect that it is an "if" command, but I can't seem to figure it out.

Just a visual example:
Weeks: 1 2 3 4 5 6 7 8
SP per week: 5 10 15 20 30 40 50 65
SM per week: 1 1 1 2 2 3 4 5

So, I need to be able to forecast my recruitment of SP (sales people), and have the formula calculate how many SM's (sales managers) I need.

Thank you.

 

[repairman615]

This may help

=IF(INT(A1/15)=0,1,INT(A1/15))

 

[repairman615]

Even better.(the previous is not accurate)

The a1 is the first cell in row that represents number of employees
then try this in the row where you want to calculate number of sales managers. copied across

=INT(A1/15)+1

 

[Rdavis]

This is getting close... but it is 1 SM short as I grow. Meaning... with 40 sales people, this formula still shows only needing 2 SM's... but I will actually need 3... as it is a 15/1 relationship... but once I have 16 SP, I will actually need 2 SM's.

 

[Rdavis]

Repairman.... thank you so much! That does the trick! I really appreciate this.

 

[repairman615]

Very Welcome. Glad to Help!

 

[Rdavis]

ok, one last question with this formula... when I have 45 sales people, it is showing that I need 4 SM's, when in reality I only need 3... but once I have 46 sales people, then I need that new SM. How can I alter this formula to take that into consideration.

Just so you understand... I am dividing commissions among the SM's that are managing the respective SP... so this is why I don't want to add the new SM until there is that "extra sp" that needs to be managed.

 

[repairman615]

Right. How about this:

=ROUNDUP(A7,0)

 

[repairman615]

=ROUNDUP(A1/15,0)

Sorry I got excited there...back to the basics.

 

[Rdavis]

Repairman, you are magic! Thank you so much. That was the answer. Where would you recommend I go to get more experience/knowledge for formulas? Thank you again so much for your help!