Forum Discussion

ZanaSkenderi1's avatar
ZanaSkenderi1
Qrew Member
4 years ago

Summary field used in a formula, need to return 0 to blank values

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

thank you for your help

------------------------------
Zana
------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years 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
    ------------------------------