A
A
Alina Shah2021-08-20 04:28:48
excel
Alina Shah, 2021-08-20 04:28:48

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

1 answer(s)
G
Grigory Boev, 2021-08-20
@Shah_Alina

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\""})
It is not clear what is happening with the second cell
if the value is 22, then write only in the first cell so that this number does not repeat in the third cell.

The same, i.e.
=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)})

Provided that the number is less than 100
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.

The same, you can even simplify
=if(A1>22;{целое(a1/10)\остат(a1;10)\if(0=(остат(a1;22));22;остат(a1;22))})

It remains to combine everything into one formula
=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 question

Ask a Question

731 491 924 answers to any question