Problems with nested IF/COUNTIF statement

Isabelle

Hi, I am trying to summarize in a parent row the information underneath.

In the child rows, information is either inputted as N/A, or as a numeric value (%).

For example, child rows are as follows:

N/A

N/A

N/A

5%

10%

I want the parent row to look at the child rows and determine if they are all N/A, in that case what should be summarized is N/A. If there is a value in one or more of the rows, then the parent row should just average out the rows that have a numerical value (%). In the example above, the parent row should return 7.5% as an average. 

Can you help put this formula together please? Thanks!

Paul Newcome

Yes. We would use a basic IF statement to specify that if all are "N/A", then display "N/A", otherwise display the average of the numerical values.

 

=IF(COUNTIFS(CHILDREN(), "N/A")

 

This will count how many are "N/A".

 

=IF(COUNTIFS(CHILDREN(), "N/A") = COUNT(CHILDREN()),

 

This compares it to the count of all of the children rows, so we are saying that if the count of "N/A"'s is equal to the count of the children (meaning all are "N/A").

 

=IF(COUNTIFS(CHILDREN(), "N/A") = COUNT(CHILDREN()), "N/A", 

 

Then display "N/A".

 

=IF(COUNTIFS(CHILDREN(), "N/A") = COUNT(CHILDREN()), "N/A", AVG(COLLECT(CHILDREN(), CHILDREN(), ISNUMBER(@cell))))

 

And this last portion says that if the logical statement is not true (meaning there is at least one numerical value), then we want to collect all of the children that are numbers and average them.