MacMost Q&A Forum • View All Forum QuestionsAsk a Question

# How Can I Set Up a Template With the Correct Formulas I Need?

Hello again

This is Derrick Johnson looking for help to create a template to help me with my hours and task allocation each month for my clients.
So I am looking to have the ability to plug in a total cost of hours performed as shown in my redline template upload (\$10,000), then I could input the hours performed in “hours per task” and have the template generate the percentage of that task as well as the cost of that percentage of the hours performed (\$10,000). I am sure once a template is created then I will be able to add a few more rows if necessary.

https://imgur.com/a/mw87Fr2
—–
Derrick Johnson

### Comments: 4 Responses to “How Can I Set Up a Template With the Correct Formulas I Need?”

2 years ago

To calculate the percentage, first get the sum. That would be the total at the bottom of column B. So that is just =SUM(B).

Then divide each value in B by the total. So in C2, you would have =B2/SUM(B). Copy and paste that in all of the cells (not the header or footer row though) of C. Now you have the percent for each. Just format all of those cells as percent instead of regular numbers, so you get 20% instead of 0.2.

Then the next column is just the exact same thing, but multiplied by the total amount. Format those cells as currency.

derrick johnson
2 years ago

I am confused with column D. I have the formula done to get the percentage column C from column B but I do not understand how to get the formula to take the percentage(Column C)and get the dollar amount of that percentage from the 10,000.00. attached is what I have so far.
Thank you
https://imgur.com/a/xFm8Tu2

2 years ago

derrick: So it looks like you entered the total in E8. So then D2 would be =C2*E\$8.
Or, you could do the calculation again so D isn't dependent on C. So it would be =(B2/SUM(B)) * E\$8

Pay special attention to the \$ in E\$8. It means that the 8 will be held constant even when you copy and paste D2 to D3, D4, etc.

derrick johnson
2 years ago

I just got it with your help.

Thank you so much!