# Conversion Rate Only Level 1

✭✭✭✭

On a sheet, I want to show the conversion rate only on the parent (Level 1) row, and blank in the associated children rows. All the reporting information is on the Level 1 row, the children rows are irrelevant. How do I tweak the formula below to report this:

=IFERROR(NETDAYS([Quote Issued Date]@row, [Expected Close Date]@row), 0)

## Answers

• ✭✭✭✭

Also for this formula, what can I add to the formula that if the cell returns a "0" value in the Parent (Level 1) row that it is left blank?

• ✭✭✭✭✭✭

The first part is an IF statement to say that if it is on the first level to run the calc.

=IF(COUNT(ANCESTORS([Primary Column name]@row)) = 0, original_formula)

The next would be another IF statement to say that if it is not equal to zero the run the calc (leaving the third potion of the IF statement will leave it blank by default).

=IF(COUNT(ANCESTORS([Primary Column name]@row)) = 0, IF(original_formula <> 0, original_formula))

• ✭✭✭✭
edited 09/03/24

@Paul Newcome Hi, thanks for getting back to me. I tried the formula in different ways and either I get #INVALID DATA TYPE, or nothing changes, it stays as "0". The Primary Column is titled [Opportunity], which I tried and get an error #INVALID DATA TYPE. I tried the formula with [Level] which indicates the Parent Row as = 1, and the formula returns with the correct count but when I change the reporting column to Convert to Column Formula, all the children rows become #INVALID DATA TYPE. I would like the cells to remain blank if there are zeros, and all the children rows to remain empty. It is also only relevant if the [Sales Stage] is Closed Won, which could help weed out the zeros.

=IF(COUNT(ANCESTORS(Level@row)) = 1, IF(NETDAYS([Quote Issued Date]@row, [Expected Close Date]@row), 0) <> 0, IFERROR(NETDAYS([Quote Issued Date]@row, [Expected Close Date]@row), 0))

• ✭✭✭✭✭✭

Are you able to provide some screenshots for context?

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!