A
A
Alina Shah2021-09-17 19:58:11
Google Sheets
Alina Shah, 2021-09-17 19:58:11

How to change the formula so that when the data is entered, it counts according to the specified conditions, and if there is no data, it does not enter zeros and other values?

https://docs.google.com/spreadsheets/d/1vqKnsN_8qQ... I

will describe the meaning of the whole formula:
1) if in cell U3 "=" or "<22," then we write this value in cell AN50
2) if U3> 22, then we write the first digit from the number in cell AN50 and the second digit in cell AO50 and the difference "-22" in the third cell.
For example 23, then "2" into cell AN50,
"3" into cell AO50,
and "1" (23-22) into cell AP50.

If the number is 44, then:
"4" in cell AN50,
"4" in cell AO50,
and "22" (44-22) in

cell AP50 the sum is not in cell U3, then do not write anything in cells AN50, AO50 and AP50.

At the moment, the formula is like this and, in the absence of data in P3 and a zero value in U3, it writes the number 22 to AP50, and there should be an empty cell:
=arrayformula((
IF(
IF(U3<=22;{U3\""}; {INTEGER(U3/10)\MOD(U3;10)\IF(0=(MOD(U3;22));22;MOD(U3;22))})=0;"";IF(U3<= 22;{U3\""};{INTEGER(U3/10)\MOD(U3;10)\IF(0=(MOD(U3;22));22;MOD(U3;22))}))
) )

With this formula, item 3 was not observed, zero was written in AN50, zero was also written in AO50, and 22 was written in AP50:
=arrayformula(IF(U3<=22;{U3\""};{INTEGER(U3/10) \OSTAT(U3;10)\IF(0=(OSTAT(U3;22));22;OSTAT(U3;22))}))

Any thoughts on how to modify the formula according to the three conditions described above?

Answer the question

In order to leave comments, you need to log in

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

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question