How can we help?
Full Site Search

How to make formulas in numeric fields return as empty values instead of 0 ?

In Ragic, if you assign any formulas to numeric fields, either the results should be empty value or 0, they'll always return 0. For example, the formula A1*1 will display as 0 instead of empty value even though you haven't typed any value in A1.

Return empty value when a single referred field is null

If you want to make a formula return empty value when there's no value in the referred field. You can use ISBLANK function to check if the field is null. Afterwards, use IF function to return the value on the results of ISBLANK function. For example, you could use IF(ISBLANK(A1),"",A1*1) to make it return empty value if A1 is empty, if not, calculate A1*1.

Having multiple conditions or referenced fields in formulas: use OR function

If the calculation might refer to more than one field, for example, to calculate A1*A2 only when both fields are not empty, and keep the output null if at least one of the fields has no value, you'll need to first use OR function with ISBLANK function to check if every referenced field is empty, and use IF function to return the value. The formula IF(OR(ISBLANK(A1),ISBLANK(A2)),"",A1*A2) would make sure that if at least one of the fields is empty, returns empty value, if not, calculate A1*A2.

Change the result "0" into empty value after calculation

By using OR function, you can combine different conditions in your formulas, if you want to make "0" turns into empty value as well, you can add the condition "original formula=0" to the new formula. For example, using formula IF(OR(ISBLANK(A1),ISBLANK(A2),A1*A2=0),"",A1*A2) to make the output null if at least one of the referenced fields is empty or A1*A2=0. If none of the conditions above are met, returns the calculation result of A1*A2.

Top of Page

    Start Ragic for Free

    Sign up with Google