How do I do a repeated function in excel?
I want the pattern to go: (Vertical)
=a1 =b1 =c1 =a2 =b2 =c2 =a3 =b3 =c3
and so on. this will go on for until:
=a400 =b400 =c400
2
-
These rows should be going vertical not horizontal.– ChrisCommented Mar 30, 2022 at 22:45
-
Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.– Community BotCommented Mar 30, 2022 at 22:55
Add a comment
|
2 Answers
Do the following steps:
- Place this formula in the 1,200 cells you need it to be in (400 row numbers * 3 column letters = 1,200):
=IFS(MOD(ROW(),3)=1,"=A",MOD(ROW(),3)=2,"=B",MOD(ROW(),3)=0,"=C")&ROUNDUP(ROW()/3,0)
They should still all be highlighted. Copy them to the Clipboard. Paste Special, Values.
Again, they should still all be highlighted. Do a
Find and Replacelooking for the=and replacing it with itself (so=in both the find and the replace boxes).
Done.
The formula has two parts to build the strings you will convert to formulas. The first is the letter, using IFS() to choose between the three choices. In doing so, MOD() is used to decide which of the three possibilities in your pattern a given row is. Often order of tests matters in an IFS() as one is actual sieving the possibilities and relying on the sieve's logic to be able to simplify the testing. But not in this case as you simply need to decide amongst three simple possibilities, each of which completely excludes the others.
The second half finds the row number to join/concatenate to the column letter the IFS() generates. It simply divides by three and rounds up. Not complicated at all!
As to the technique, basically you are building the strings you need converted into formulas. Once you have them generated, you convert them into literal text strings (all the "formula" aspects gone, just the resulting strings now in place) with paste special, values. Finally, you coerce Excel into changing its treatment of those strings from "you are a text string" to "you are a formula." And done.
(If Excel were a complete program, not one with bizarre... disabilities... you'd have a function, say TEXTTOFORMULA(), that you'd wrap the shown formula in and it'd do exactly that. In a wonderful world, it'd even allow for a two step Paste Special, in one of three ways that come to mind, in which the first would paste special the generated formula all by itself, and doing it immediately again would place the resulting values in the cells. The second would be that it'd use its Paste Special, Formulas, to, in this case only, place the resulting formulas in the cells. The third would be you just do the whole thing twice, the first placing the result of this formula — the actual generated formula, but as a formula, not text — and the second copy, paste, special would put its generated values in the cells.)
This kind of unwrapping of a 2D table to a 1D column is easily done using INDEX. Assuming you start in row 1, we use the current row number as a index into the table, splitting it into x and y using divide by 3 and mod by 3:
=INDEX($A:$C, INT((ROW()-1)/3)+1, MOD(ROW()-1,3)+1)
answered Mar 31, 2022 at 1:33
