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.

Thank you for your help

View post on imgur.com


—–
Derrick Johnson

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

    5 months 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
    4 months 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

    4 months 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
    4 months ago

    I just got it with your help.

    Thank you so much!

Leave a New Comment Related to "How Can I Set Up a Template With the Correct Formulas I Need?"

:
:
:
0/500 (500 character limit -- please state your comment succinctly and do not try to get around this limit by posting two comments)