Deduce formula from spreadsheet calculation

[dalien88]

Hi all,

I hope this is the right board to post this question! I have a spreadsheet that was forwarded to me, it has a couple of formulas for a calculation of employee contributions to a stock/equity program. I need to figure out what the formula or expression would be, in mathematical terms, to make a computation that can be described thusly:

• first, add up an employee's total number of months worked at the company,
• then divide 5% - the percentage of the employee's contribution to the program - by the total of the previous sum,
• multiply the result by the total number of months worked

Here's a link to a Google Sheets document with all the information, including the description above — hope I explained it all correctly here...

Any questions (or answers!) please email me, Danny, at dvillalobos@hellovacay.com -- thanks all!

 

[tkhunny]

Doe it ALWAYS produce 5%?

 

[Dr.Peterson]

I have a spreadsheet that was forwarded to me, it has a couple of formulas for a calculation of employee contributions to a stock/equity program. I need to figure out what the formula or expression would be, in mathematical terms, to make a computation that can be described thusly:

• first, add up an employee's total number of months worked at the company,
• then divide 5% - the percentage of the employee's contribution to the program - by the total of the previous sum,
• multiply the result by the total number of months worked

So, you've got the spreadsheet formula worked out, and you've expressed it in words; are you saying that for some reason you need it expressed so that a mathematician will understand it? Why?

Well, actually, your words don't quite say what I see in the spreadsheet; here is my interpretation of what you mean:

• first, add up the total number of months worked at the company by all employees;
• then divide 5% (the percentage of the total contribution to the program that is made by all employees) by the previous sum to find the percentage per month worked;
• multiply the result by the total number of months worked by an individual employee to find the percentage of the total contribution that is deemed to have been made by this individual.

Is that right?

Perhaps this is what you want, where m_i is the number of months worked by the i'th worker:

\(\displaystyle p_i=\dfrac{0.05m_i}{\sum_i{m_i}}\)
​

 

[dalien88]

Thanks all for your answers. A colleague posted the following, thinking it might be as simple as this...

iM/tM*5=Equity

where i = individual months and t = total months

In any case it appears they got the answer they wanted, so—thanks again all.

 

[Dr.Peterson]

Thanks all for your answers. A colleague posted the following, thinking it might be as simple as this...

iM/tM*5=Equity

where i = individual months and t = total months

In any case it appears they got the answer they wanted, so—thanks again all.

I think that's essentially what both of us said, except that you are using 5 instead of 5%.