calculating precentage

I have a conflict here, I want to calculate the percentage of hiring as shown, but the input given to me is per row as shown in the completion % column..but I wanted to be weighted by the number of hiring as I don't think that hiring 1 person is as hiring 15!

so I added a helper column called Number of H&T and inserted the number then make an equation for the sum as shown in the brown row then for another added column (Count percentage) I make equation (=[Completion %]@row / [Number of H&T]$39) and at the brown row make =AVG([Count precentage]40:[Count precentage]45), I feel that is a long loop and don't know if that is correct or not, then I have to calculate all hiring for the operation which are separate in a different row hence I want to calculate at the sheet summary ..which equation I should use also?

thanks in advance


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi @Rana HUSSEIN ,

    I'd add another helper column [Wgt] with a formula =[Number of H&T]@row * [completion %]@row

    In you parent row, the weighted percent completion would be:

    =Sum(Children([wgt])/Sum(Children([number of H&T]))

    Is that what you're looking for?


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Rana HUSSEIN
    edited 02/08/21

    thanks, a lot mark for your reply but it gives me #UNPARSEABLE, I make something but don't know if that is right or not .... in the parent row I inserted this equation

    =IF(COUNT(CHILDREN([Completion %]@row)) = 0, "", AVGW(CHILDREN([Completion %]@row), CHILDREN([Number of H&T]@row)))

    and as I have like 5 parent rows like this I count there averages

    =AVG([Count precentage]41, [Count precentage]52, [Count precentage]63, [Count precentage]74, [Count precentage]80, [Count precentage]90)

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭

    Hi Rana, Unparseable is a formatting issue. Try:

    =IF(COUNT(CHILDREN([Completion %]@row) = 0, "", AVGW(CHILDREN([Completion %]@row), CHILDREN([Number of H&T]@row))



    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!