Answer the question
In order to leave comments, you need to log in
How to space numbers into three cells under certain conditions?
https://docs.google.com/spreadsheets/d/1vqKnsN_8qQ...
I can't find a universal formula to:
1) enter values from 1 to 22 only in the first cell, and not repeat this value in the third cell.
2) if the value is 22, then write only in the first cell so that this number does not repeat in the third cell.
3) if the value is greater than 22, for example 23, then write 2 and 3 in the first and second cells, respectively, and the difference 1 (23-22=1) - in the third cell.
If, say, the value is 49, then we enter 4 and 9 in the first and second cells, respectively, and the difference is 3 (49-22 \u003d 27 is more than 22, subtract 22 = 5 again), then we enter 5 in the third cell.
It is important to subtract 22 so many times until there is 22 or less.
4) if the remainder is 22, for example, when 88 (88-22 \u003d 66 - this is more than 22, then minus 22 \u003d 44 - again more than 22, subtract 22 \u003d 22), then we enter 8,8,22 in three cells, respectively.
Now, in some modifications of the formulas, the remainder 22 is not written, only 8 and 8 are separated.
What formulas were used are written in the table.
You can work with formulas in cells:
Z17
AD17
AH17
You can enter data (letters) in green cells in order to appear necessary for checking the value.
I would be grateful for the correction of the formulas.
Answer the question
In order to leave comments, you need to log in
values from 1 to 22 entered only in the first cell and did not repeat this value in the third cell.
=if(A1<22;{a1\a1\""})
if the value is 22, then write only in the first cell so that this number does not repeat in the third cell.
=if(A1<=22;{a1\a1\""})
if the value is greater than 22, for example 23, then write 2 and 3 in the first and second cells, respectively, and the difference 1 (23-22=1) in the third cell.
=if(И(A1>22;0=остат(a1;22));{целое(a1/10)\остат(a1;10)\остат(a1;22)})
if the remainder is 22, for example, when 88 (88-22 \u003d 66 is more than 22, then minus 22 \u003d 44 is more than 22 again, subtract 22 \u003d 22), then we enter 8,8,22 in three cells, respectively.
=if(A1>22;{целое(a1/10)\остат(a1;10)\if(0=(остат(a1;22));22;остат(a1;22))})
=arrayformula(if(A1<=22;{a1\a1\""};{целое(a1/10)\остат(a1;10)\if(0=(остат(a1;22));22;остат(a1;22))}))
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question