Conversion Rate Only Level 1

Michelle Maas

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)

Michelle Maas

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?

Paul Newcome

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))

Michelle Maas

@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))

Paul Newcome

Are you able to provide some screenshots for context?