% of children

N J

Hi,

 

I want to calculate % of checked children in the sheet below, can anyone help me?

 

https://app.smartsheet.com/b/publish?EQBCT=41482d287b58426b801642f8c4cd31f0

 

Niclas

J. Craig Williams

Done - documenting now

 

 

Gecko

Thanks Craig, very useful.

J. Craig Williams

Here's a screen shot of the sheet:

 

 

I added the second group because there's a few lessons here.

 

1. The original data (rows 1-11) is not in a hiearchy.

The highlighted cell in [Column7] is:

 

 

=COUNTIF([Column7]2:[Column7]11, 1) / COUNT([Column7]2:[Column7]11) * 100 + "%"

 

Because [Column7] is a check box column type, if you want a number higher than 1 (1=checked), then you can force it to be text by using the + "%".

You could also use + "" but then 40 might seem like a number -- but it isn't - it is text.

 

2. I added the second section of data and indented the rows below row 13.

This allows the CHILDREN function to be used.

The highlighted function is

 

=COUNTIF(CHILDREN([Column7]13), 1) / COUNT(CHILDREN([Column7]13)) * 100 + "% checked "

 

This is also changed to text by adding + "% checked" but you could remove that and have the number be a number since the Primary Column is a text/number type.

 

Both count the number of checked boxes (using COUNTIF) and the total count of the rows beneath the first one.

Multiply by 100 to get to percentage.

 

Hope this help and makes sense.

 

Craig

 

 

J. Craig Williams

OK, that is weird - my paragraphs are out of order.

Fixed it - but not sure wtf.

 

Craig

N J

Perfect, thank you for your help Craig!