A
A
Alina Shah2021-09-13 11:51:41
Google Sheets
Alina Shah, 2021-09-13 11:51:41

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

3 answer(s)
A
Alina Shah, 2021-09-19
@Shah_Alina

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))}))

I
idShura, 2021-09-13
@idShura

Use Conditional Formatting

G
Grigory Boev, 2021-09-14
@ProgrammerForever

=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 question

Ask a Question

731 491 924 answers to any question