# Discussions

View Only

## Summary field used in a formula, need to return 0 to blank values #### Zana Skenderi17 days ago • #### 1.  Summary field used in a formula, need to return 0 to blank values

Posted 17 days ago
Hi,

I have a summary formula of a checkbox to give me a count, in the parent table. I am then taking that field and dividing it with itself. In turn trying to use the formula in a summary formula of a report. but if that particular person has a blank value, it does not work in the formula. I have figured out the error is occurring because it is not calculating the blank as zero. Any help with this formula would be greatly appreciated. I maybe think I need to do the blanks as zeros in the summary formula but not sure how to do it there.

This is the formula field that I need to treat blanks as zeros - [Extensive Logged Report Types]/[Extensive Logged Report Types]

This is the summary formula of the summary report - (((([Number of Charts]-[# of No Info/Search Fee (tot)]-([Extensive 1 Calculation (tot)]))/([Number of Charts])*0.03)*([Number of Charts])))

------------------------------
Zana
------------------------------

• #### 2.  RE: Summary field used in a formula, need to return 0 to blank values

Posted 17 days ago
Its hard to understand quite what you are doing here

this was confusing

This is the formula field that I need to treat blanks as zeros - [Extensive Logged Report Types]/[Extensive Logged Report Types]

That looks like you are dividing a field by itself so that will always = 1 unless the value for [Extensive Logged Report Types] is zero because you cant divide by zero.

Then you have this formula here

This is the summary formula of the summary report - (((([Number of Charts]-[# of No Info/Search Fee (tot)]-([Extensive 1 Calculation (tot)]))/([Number of Charts])*0.03)*([Number of Charts])))

If the problem is that sometimes the [Number of Charts] is zero, you can do this.

IF([Number of Charts]=0,0,
(((([Number of Charts]-[# of No Info/Search Fee (tot)]-([Extensive 1 Calculation (tot)]))/([Number of Charts])*0.03)*([Number of Charts]))))

If the problem is that you have an actual summary field from a relationships and it is showing a blank instead on a zero when used in calculations, then there is a setting on the field properties to treat blank as zero.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------