vendredi 12 avril 2019

Nested if (or) in array for heading cell in google sheets is only validating the first 2 conditions

I am trying to make a formula in google sheets that works as an array in the first cell of a column, displaying the title of the column, and also triggers a text change in the rest of the column depending on a number range from another cell. All this for an array that works along data from a google forms.

So far I previously had this working but you need to manually drag it down:

=IF(K1=0, "Finalizado",IF(AND(K1>=0.000001, K1<0.49),"En Tiempo", IF(AND(K1>=0.5, K1<0.75),"Pendiente", iF(AND(K1>=0.75, K1<1), "Retrasado",iF(AND(K1>=1), "Fuera de Tiempo", "Undefined")))))

Then, when I make it into an array that is set on the first cell of the column it only validates the first two conditions, so it only shows either 'finalizado' if the referenced cell is 0 or 'En tiempo', not to mention that once it gets to a point where there is no more referenced data, it just keeps repeating 'finalizado on each next cell.

={"Estatus";ArrayFormula(IF(K2:K=0, "Finalizado",IF(or(K2:K>=0.000001, K2:K<0.49),"En Tiempo",IF(and(K2:K>=0.5, K2:K<0.75),"Pendiente",iF(and(K2:K>=0.75, K2:K<1), "Retrasado",iF(AND(K2:K>=1), "Fuera de Tiempo"))))))}

I think the problem might be around the third if.

Aucun commentaire:

Enregistrer un commentaire