Calculated Fields auto return 0.00

GerryPowell

I am making a system for calculating student Exam results and have a problem with the calculations. My teachers input the scores for each section of the exam using ENUM dropdowns and these are the added together to give a score for each part with a total divided by the the total points available for example:

ROUND(
((SUM(
LIST(
DECIMAL([READING UOE P1]),
DECIMAL([READING UOE P5]),
DECIMAL([READING UOE P6]),
DECIMAL([READING UOE P7]),
DECIMAL([READING UOE P8])
)
) / 50) * 10) * 10
) / 10

This works but if this part of the exam is not done I need the calculated to field to remain blank. At the moment every calculated field returns a zero. It is possible that a students gets a score of zero in which case the calculation will return a zero and this is included in the ENUM dropdown. At the end of the exam an average is taken of the scores for each section done. With the zeros included the average is always of the five parts when the students may only have done three.

So my question is how to eliminate the zeros if a part is not done from the calculated fields.

Suvrutt_Gurjar

If understanding of your requirement is correct, please try

IF(

ISBLANK(LIST(
DECIMAL([READING UOE P1]),
DECIMAL([READING UOE P5]),
DECIMAL([READING UOE P6]),
DECIMAL([READING UOE P7]),
DECIMAL([READING UOE P8])
)), NUMBER(""),

ROUND(
((SUM(
LIST(
DECIMAL([READING UOE P1]),
DECIMAL([READING UOE P5]),
DECIMAL([READING UOE P6]),
DECIMAL([READING UOE P7]),
DECIMAL([READING UOE P8])
)
) / 50) * 10) * 10
) / 10

)

The above suggestion assumes that the fields [READING UOE P1]....[READING UOE P8] are dropdown fields and these have not been selected by the user and are simply blank when the exam is not done.

GerryPowell

Thanks for the quick reply but even though this looks good, on testing the
expression the cell with the calculation still shows 0.00 and not blank. I
need too be able to differentiate between parts of the exam tried but
failed with a possible zero reult and those not done with no result. If the
parts are not done they should not be considered in the final average
otherwise even if the result is zero they result should be included.

jerrygen

Hi GerryPowell,

To handle this, you can adjust your formula to check whether all inputs are blank before performing the calculation. If none of the fields have values (i.e., the section wasn’t done), the formula should return a blank instead of 0. You can wrap your calculation in a condition like IF(COUNTBLANK(...fields...) = TOTAL_FIELDS, BLANK(), your_formula). This way, only actual scores—including zero when genuinely entered—are counted, and untouched sections won’t affect the average.

Regards

Suvrutt_Gurjar

@GerryPowell wrote:

Thanks for the quick reply but even though this looks good, on testing the
expression the cell with the calculation still shows 0.00 and not blank

My testing shows that an expression of NUMBER("") allocates a blank. So if

ISBLANK(LIST(
DECIMAL([READING UOE P1]),
DECIMAL([READING UOE P5]),
DECIMAL([READING UOE P6]),
DECIMAL([READING UOE P7]),
DECIMAL([READING UOE P8])
))

evaluates to a blank, the result should typically be blank as stored in the backend. It will not show in detail and form view.

Please go through the post below

Solved: How to get Form for a new record with un-required ... - Google Cloud Community