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
------------------------------
Question
Summary field used in a formula, need to return 0 to blank values
This topic has been closed for replies.
+24Its 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
------------------------------
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
------------------------------
Sign up
Already have an account? Login
Login to the community
Login with Quickbase US – quickbase.com Login with Quickbase EU – quickbase.eu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.