Calculating Percentages in Smartsheet based on the parent value

Options
Anoop Phago
Anoop Phago ✭✭✭
edited 01/17/23 in Formulas and Functions

I have two columns in my sheet. One is for the data and the other is for the percentage. In the data column, I have 5 rows with numbers 1, 2, 3, 4, and 5. The total of these numbers is 15, which is the parent value. I want to use a single formula in the percentage column to calculate the percentage of each of these 5 rows based on the parent value. I want to be able to drag this formula or just change it to a formula column for all the other rows. The expected result for the 5 rows should be 6.67%, 13.33%, 20%, 26.67%, 33.33% respectively as shown in the calculation below.

first child row will be (1/15)*100 = 6.67%,

second child row will be (2/15)*100 = 13.33%,

third child row will be (3/15)*100 = 20%,

fourth child row will be (4/15)*100 = 26.67%,

fifth child row will be (5/15)*100 = 33.33%.

Answers

  • Samuel Mueller
    Samuel Mueller Overachievers
    edited 01/17/23
    Options

    @Anoop Phago I think this will work

    =data@row / parent(data@row) and then format that column to percent.

    might need to put that formula in "iferror"

    =iferror( data@row / parent(data@row) , "")

  • Anoop Phago
    Anoop Phago ✭✭✭
    Options

    @Samuel Mueller thanks for the reply, but I have one column with child-parent in the same column (i.e Data Column), and I have added the parent row, adding up the child rows. I need to calculate the percentage in the next column (i.e Percentage Column). There are various more sites, and I want to be able to drag that formula from top to bottom.



  • Samuel Mueller
    Samuel Mueller Overachievers
    Options

    @Anoop you should be able to take this formula and put it into the percentage column and turn it into a column formula.

    =[data column]@row / parent([data column]@row)

    you may want it like this if you area getting division errors:

    =iferror([data column]@row / parent([data column]@row), "")

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!