Skip to main content
Participant
August 12, 2024
Question

Calculate average with color conditional formatting

  • August 12, 2024
  • 2 replies
  • 288 views

I want to create a form where you enter values x1-x8 which return an average (avg), and if the average is <0,25 and >0,50 the field should turn red.

I have used the following script in "Custom calculation script":

var total = 0;
var avg = 0;
for(var i=1; i<=8; i++){
if(this.getField("x"+i).valueAsString != "" && Number(this.getField("x"+i).valueAsString) != 0){
total += Number(this.getField("x"+i).valueAsString);
avg++;}}

if(avg != 0)
event.value = total/avg;
else
event.value = "";

var v = Number(event.value);
if (v<0,25 && v>0,50) {event.target.fillColor = color.red;}
else if (v>=0,25 && v<=0,50) {event.target.fillColor = color.transparent;}

The problem is that if the field turns red it stays red - it does not go back to transparent when the average value changes.

This topic has been closed for replies.

2 replies

Nesa Nurani
Community Expert
Community Expert
August 12, 2024

It's not possible for a number to be both less than 0.25 and greater than 0.50 at the same time, you wanted to use || (or) instead of && (and):

if (v < 0.25 || v > 0.50)

You may also want to check that the field is not empty or 0 before changing color.

Bernd Alheit
Community Expert
Community Expert
August 12, 2024

Change 0,25 to 0.25 

and 0,50 to 0.50