Percentage of Child Rows with Check Mark

craigwaco

Hi,

I'm trying to get a Percentage of Child Rows with Check Mark. Can anyone help out?

=COUNTIF(CHILDREN(), 1) + ""

returns the count of checked boxes.

=(COUNT(CHILDREN(), 0) + ""

returns the count of total rows, check or unchecked.

So I divided the two, but I get #INVALID OPERATION error.

 

Any thoughts?

L_123

you are converting the value inside of the cells into a text value by adding the space. I assume you are doing that to format the text to the center. Instead you should use the formatting tools. If you really feel the need to keep the space there you could probably get away with using the value() formula, but it's not really recommended in this case.

 

=Value(Aconex@row)/value(Procore@row)

J. Craig Williams

This formula

=(COUNT(CHILDREN(), 0) + ""

is incorrect (it is off by 1)

=COUNT(CHILDREN()) + ""

is.

Craig

 

craigwaco

Thanks Luke,

I'm not trying to divide Aconex into Procore.

I'm trying to Divide the number of checked Aconex boxes into the total number of Aconex boxes.

=(COUNTIF(CHILDREN(), 1) + "") / (COUNT(CHILDREN(), 0) + "")

 

craigwaco

J.Craig,

=(COUNTIF(CHILDREN(), 1) + "") / (COUNT(CHILDREN()), 0) + "")

I used this, but it's not working.

J. Craig Williams

Use Luke's suggestion of VALUE() to get your text into numbers.

Remove the trailing 0 from your COUNT formula.

If you are putting this formula in a Checkbox column, you need to convert it back to Text again (or skip it if you are replacing your current counts 4 and 16)

=(COUNTIFS(CHILDREN(),1) / COUNT(CHILDREN())) + ""

Craig

L_123

=COUNTIF(CHILDREN(), 1) / COUNT(CHILDREN()) + ""

 

Thats a neat trick using the quotes to change the format to be acceptable to a boolean column. Hadn't seen that before. 

 

Try that formula. It seemed to work for me when I tested it.

L_123

Hah

craigwaco

This gives me a #Divide By Zero error.

 

 

craigwaco

If anyone wants to take a try at it, here you go:

<IFRAME WIDTH=1000 HEIGHT=700 FRAMEBORDER=0 SRC="https://app.smartsheet.com/b/publish?EQBCT=bd662b969cf048708c879cd9ae8ba4d8"></IFRAME>

 

J. Craig Williams

In a Checkbox column?

It should not if you have children (checked or unchecked).

Craig

 

J. Craig Williams

Fixed. You had no children (rows need to be indented)

Craig

craigwaco

I just saw that and changed it, and it works.

Thank you so much J.Craig!

craigwaco

The result reports as .25 instead of 25%.

Do you have any thoughts on fixing this?

L_123

Highlight the cell and click the little % up by the dollar sign in the top bar

craigwaco

That doesn't work.