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?

Find more posts tagged with

Construction

Formulas

Project Management

Comments

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:

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.

Sign in to join the conversation!

Quick Links

Trending discussions

How to record date without time
I would like to use automation on a sheet to record the Open and Closed date for a given RAID item. I don't want to record the time, however. Is there a way to exclude a time stamp from the automation? If it is not possible to exclude the time stamp, what formula would I need to use to extract the datae and time into…
Prevent due dates from falling within the same calendar week?
Hi. I am working on a (roughly) annual document review system, in partnership with another team in my organization. They have requested that they receive one document to review per week. But there are more documents to review than there are weeks in the year. So I need to figure out a method for ensuring that I don't send…
count if value is between a date range (reference between 2 sheets)
Hi team i have 2 sheets for material inventory. the first is to record witch equipment is ship out for repair and the other is the total of equipment available. the first is set as follow: equipment type, date out, date return, quantity (negative number) the other one is as follow: each row is for a single date with the…