The quick answer
In the saved search's Formula (Currency) result, replace {amount}/NVL({quantity},0) with {amount}/NULLIF(NVL({quantity},0),0). NULLIF turns a zero denominator into null, so the ratio stays blank instead of stopping the search. Keep the existing summary type and verify a real zero amount still produces 0.
The screenshots use sample data. Person and operating-company names are fictional. Use your own records when following the steps.
Before you start
You need permission to edit the transaction saved search and inspect its source lines. This example uses Unit Price Source Lines to check the five rows behind Item Unit Price Review. Both searches use the same criteria and transaction-line selection. The grouped search reports the Maximum row-level unit price for each Item; it does not calculate a weighted average.
01. Review the source rows and failure
Open Lists > Search > Saved Searches, then run Unit Price Source Lines. Note the two Desk Bracket rows (120 ÷ 4 and 20 ÷ 2), the zero quantity for Cable Sleeve, the blank quantity for Freight Allowance, and Sample Insert's zero amount with quantity 4. These are existing test rows, not quantities to enter on an invoice.
Run Item Unit Price Review. The search fails. Confirm that the failure is caused by division by zero before changing the formula; an invalid field reference needs a different fix.
02. Guard the denominator
Return to the search definition and open Results. Locate the Formula (Currency) row. It uses Summary Type: Maximum and Function: Round to Hundredths. The original expression, {amount}/NVL({quantity},0), converts a missing quantity to zero and then attempts division by zero.
Open the formula editor. Change only the denominator handling to {amount}/NULLIF(NVL({quantity},0),0), click Set, and commit the result row with OK. Keep Maximum, the rounding function, and the label Maximum Valid Unit Price. An undefined ratio should be blank; wrapping the whole expression in NVL(...,0) would incorrectly turn it into a zero price.
03. Verify the results
Click Save & Run. The four item groups should appear. Desk Bracket returns 30.00, the larger of its valid row prices 30 and 10. Cable Sleeve and Freight Allowance stay blank because their quantities are zero or missing. Sample Insert returns 0.00 because its amount is genuinely zero and its quantity is 4.
Check the result
- The search completes with the intended four item groups.
- Zero or missing quantity produces a blank ratio.
- A zero amount with nonzero quantity produces 0.00.
- Maximum reports the largest valid row price, not total amount divided by total quantity.
Keep the original search population. Excluding zero-quantity rows just to make the formula run would change which items appear.
Common questions
Why doesn't NVL alone prevent division by zero?
NVL({quantity},0) changes a missing quantity to zero, which is still an invalid denominator. NULLIF(...,0) changes that zero denominator to null before division.
Why is an Item group's result blank?
If every row in the group has a zero or missing quantity, each row-level ratio is null. Maximum has no valid value to show, so the group stays blank rather than displaying a fabricated zero.
Does Maximum calculate an average price?
No. It selects the largest valid row-level ratio in each Item group. Desk Bracket has ratios 30 and 10, so it shows 30. A weighted amount-per-unit calculation is a different search design.
Will a real zero price still appear?
Yes. A zero amount divided by a nonzero quantity is 0, so Sample Insert shows 0.00. A zero quantity leaves the ratio undefined and blank.
Does this formula change the transactions?
No. It changes the saved search's result formula. The source amounts and quantities remain as they were.