Hide Divide by Zero Error

Hello,

I am having issues with creating the correct formula that calculates the average of the children rows while hiding the divide by zero errors for columns that do not have any data.

Could someone please help? I tried the following

=IFERROR(AVG(CHILDREN([Column]@row],"")))

Thanks so much!

Find more posts tagged with

Sheets

Healthcare

Formulas

Project Management

Comments

Paul Newcome

You just need to adjust some of your parenthesis...

=IFERROR(AVG(CHILDREN([Column]@row])),"")

eric.o

Hello,

Happy to help! Paul is correct adjusting the parenthesis would work, don't forget to remove the bracket. Also, the Children Function will reference all child rows within the same column, you may not need the [Column]@row part of the formula.

This is further outlined here: https://help.smartsheet.com/function/children

The formula could look like this:

=IFERROR(AVG(CHILDREN([Column]@row),""))

=IFERROR(AVG(CHILDREN(),""))

Cheers,

Eric

Smartsheet Support

Thanks so much!

Thank you.

Happy to help.

I have a similar question, though I have a typical If function. The Iferror doesn't seem to replace it to fix. The formula currently for Fee Spend Percentage is:

=[Spent (Ajera)]@row / [Contract Amount]@row

applied to column. The formula currently for Under Fee is:

=IF([Project Complete (Percentage)]@row > [Fee Spent (Percentage)]@row, "Yes", "No")

applied to column. I would prefer that if I schedule a project but the contract/fee information is not input, it would not identify anything at all instead of #DIVIDE BY ZERO.

As an aside, would anyone have any idea of how to grab Ajera data instead of needing to input it manually?

I appreciate any help!

Genevieve P.

Hi @Tehren

When you say that the IFERROR function doesn't help, what happens? Are you seeing the same error?

The IFERROR function for your formula would be structured as follows:

=IFERROR([Spent (Ajera)]@row / [Contract Amount]@row, 0)

Did that work?

Cheers,

Genevieve

Sign in to join the conversation!

Quick Links

Trending discussions

Set contents of two columns into 1
Hi - trying to figure out a formula that combines two columns into one long column, maintaining contents in multiple rows. Noting that the number of items in each column will change as these are each using the index/distinct to summarize a separate sheet. For example: Thanks for any help!
Unparsable formula; Sheet summary SUMIF formula
Can anyone help why this formula is coming up as unparseable? I'm creating a sheet summary, where it totals the Estimated GP (Est. GP) column if the Status column has the "Quoted" drop-down option is selected. Formula i've used: =SUMIF(Status:Status,"Quoted",Est. GP:Est. GP) Thanks a lot!
INDEX/MATCH Most Recent Entry
I am trying to pull from a sheet that is essentially a list of new records that are entered weekly. To keep things clean I have a helper sheet that just lists the project name once and the rest of the columns pull in through an INDEX/MATCH. I am trying to have that helper sheet pull in only the most recent entry. There is…