Answer the question
In order to leave comments, you need to log in
How to make the formula not display zeros or they are not written by the arrayformula formula?
https://docs.google.com/spreadsheets/d/1vqKnsN_8qQ...
There is this formula:
=INDEX(SORTN(TRANSPOSE({
IF({AN50:AP50}="";"";--{AN50:AP50 });
TRIM(QUERY(
IF("" & TRANSP({AN50:AP50})="" & {AN50:AP50};IF({AN50:AP50}="";"";--{AN50:AP50 }););
;
9^9))
});9^9;2;1;1);;2)
It is necessary that it does not display zeros in cell H42 if they are in the range AN50:AP50, and also and 22 from AP50
OR in cell AN50 in the formula, also set a condition, if there is no data in cell U3, then do not write anything in the range AN50:AP50. And then there is no data, but the numbers are entered and we consider that they do not exist.
Formula in AN50
=arrayformula(IF(U3<=22,{U3\""},{INT(U3/10)\MOD(U3,10)\IF(0=(MOD(U3,22)),22,MOD(U3 ;22))}))
Answer the question
In order to leave comments, you need to log in
the solution was found independently: I
added "--" to the main formula:
=arrayformula(IF( -- U3<=22;{U3\""};{INT(U3/10)\MOD(U3;10)\IF(0 =(OSTAT(U3;22));22;OSTAT(U3;22))}))
=arrayformula(if(данные=0;"";данные))
Substitute your formula for the data . Then at zero values there will be empty cells.
If the data itself is not important, you only need a visual, then use conditional formatting, there is a section for displaying zeros.
0;-0;;@
This template should fit
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question