Caculation on Report

[Julia T]

I have three columns and I want to caculate the % of the sum of two columns
but if the either of two columns have a 0 I just want it to be blank.

I have this in the forumla and it half works.
ie:

Invoiced Cost Gross Profit GP%
(Number) (Number) (Invoiced-Cost) (Gross Profit/Invoiced)
0.00 1.00 -1.00 "Blank" This Works
100.00 0.00 100.00 100% but I want this
to be blank if
Cost is 0.00
100.00 50.00 50.00 50%

This is what I have now.

=IIf([Invoiced]=0," ",[GP]/[Invoiced]

need some help - please

 

[Allen Browne]

If you use a string (the space), Access will treat the field as text.
Use Null for a field that has no entry.

Try:
=IIf([Invoiced] = 0, Null, ([Invoiced] - [Cost]) / [Invoiced])