With consistent reporting, whether it be annual, quarterly, or monthly, you might have between one and several thousand stakeholders reporting into a single impact indicator. Often, some of these stakeholders may not have results for a given period due to gaps in funding, operational issues, or because the indicator is part of a form they must complete as part of an initiative they do not produce results for. This raises an important question: Should they enter a zero or report “nothing to report”? And what are the impacts if we sum or average these zero results?
The Impact of Zeros in Summing and Averaging Data
When considering the impact of zeros in your reports, it’s crucial to understand how they can drastically affect the outcomes of sums and averages. Let’s look at the following tables for a simple mathematical illustration, in an example where there are three stakeholders reporting into a single indicator. The first stakeholder has no data or nothing to report for the Indicator.
Sum of Value:
0+5+5
= 10
Average of Values (Including Zero):
0+5+5 / 3
= 10 / 3
Average Calculation: Average of Values (Excluding Zero, Using NULL)
NULL + 5 + 5 / 2
= 10 / 2
= 5
Explanation
In 'Sum of Value:', summing the values is straightforward, and the zero contributes to the total sum without significant distortion. However, in 'Average of Values (Including Zero):', including the zero in the average calculation lowers the average significantly. By excluding the zero (or using NULL) as shown in 'Average Calculation: Average of Values (Excluding Zero, Using NULL) ', the average reflects the actual performance of the contributing stakeholders more accurately.
Correct Input Value Handling
To ensure accurate data reporting, we have introduced a new feature in our application. This update focuses on improving data integrity by handling zero and NULL inputs effectively.
Sum or Average Icon
On each reporting page, an icon will display whether the current input values are being summed or averaged.
Enforcing NULL Inputs
When a report is submitted with 'no data', stakeholders must click a "Zero" button at the top of the form to indicate no data, which will record a Zero value for the sum indicator and a NULL value for the average indicators.
Zero Value Prompts
If a user tries to input a zero for an average calculation, the system will prompt them with, "Are you sure?" and offer two options:
-
- Yes, I'm sure: This will input the zero value.
- No, I do not have a number: This will input a NULL value.
By adopting these practices, you can ensure that the reported data accurately reflects the performance and impact of your stakeholders, providing more reliable insights and preventing zeros from skewing your results.
We believe this feature will greatly enhance your data reporting process, ensuring more precise and meaningful outcomes.