google sheets - How to Extend a Formula to Apply to All Rows Using MAP or ARRAYFORMULA? - Stack Overflow

admin管理员组

文章数量:1202361

I’ve got a working formula that generates the due dates, descriptions, and amounts for a payment plan in Google Sheets, but it only works for the first row of the input table. I need to extend it so that it applies to every row in the table.

Here’s the input table (starting from row 4):

Description	Frecuency (Months)	Number of Installments	Amount ($)	Start Date
Plan A	3	3	$15,000.00	1/1/2023
Plan B	12	5	$1,000.00	1/3/2024

I’ve got a working formula that generates the due dates, descriptions, and amounts for a payment plan in Google Sheets, but it only works for the first row of the input table. I need to extend it so that it applies to every row in the table.

Here’s the input table (starting from row 4):

Description	Frecuency (Months)	Number of Installments	Amount ($)	Start Date
Plan A	3	3	$15,000.00	1/1/2023
Plan B	12	5	$1,000.00	1/3/2024

And here’s the output (which works for the first row):

Expiration date	Description	Amount ($)
1/1/2023	Plan A (1/3)	$15,000.00
1/4/2023	Plan A (2/3)	$15,000.00
1/7/2023	Plan A (3/3)	$15,000.00

I’m using the following formula to generate the due dates and descriptions:

=map(sequence(D4,1,0), lambda(i,let( j,i*C4, hstack( FECHA.MES(F4,j), join("",B4," (", i + 1, "/", D4, ")"), E4 ) )))

Problem:

It works for the first row, but I need it to apply to all rows. The MAP function seems to process only the first row and doesn’t extend the calculation for other rows.

What I’ve Tried:

I’ve used ARRAYFORMULA, but I can’t seem to apply it across all rows in the input table. I also considered using SEQUENCE with MAP, but it only returns the result for the first row.

PD: thanks to user "doubleunary" for coming up with the formula that helped me.

• google-sheets
• google-sheets-formula

Share Improve this question Follow edited Jan 22 at 3:49 Ken White 126k1515 gold badges234234 silver badges463463 bronze badges asked Jan 21 at 14:57 MartinMartin 3555 bronze badges 3

• 1 It looks like you included code from somewhere else. If you're using someone else's work without giving them credit, that constitutes plagiarism, which is not welcome on Stack Exchange. To fix it, edit to make sure you do ALL the following: 1.Include a link to the source, 2. Mention the author's name, and 3. Quote the copied content. For more details, see referencing help and this FAQ. – TheMaster Commented Jan 22 at 0:56
• 1 Though you've given credit, you didn't link to the source, which is a violation of the CC-BY-SA license. – TheMaster Commented Jan 22 at 0:57
• I see, i will add a link to my previous question's answer, w – Martin Commented Jan 22 at 2:39

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

Use Array Manipulation Formulas (WRAPROWS & TOROW)

With the help of the previous formula, I transformed the answer by adding WRAPROWS and TOROW functions to circumnavigate the limitation of MAP/BYROW functions (which is the fact that these functions cannot have outputs of more than 1 row per iteration). The new formula looks like this:

=WRAPROWS(TOROW(BYROW(A2:E3,
       LAMBDA(x,
              LET(description, INDEX(x,,1),
                  freq, INDEX(x,,2),
                  installment, INDEX(x,,3),
                  amount, INDEX(x,,4),
                  start, INDEX(x,,5),
                  TOROW(BYROW(sequence(installment, 1, 0),
                        lambda(i,
                               let(j, i * freq, 
                                   hstack(edate(start, j), 
                                   join("",
                                        description,
                                        " (",
                                        i + 1,
                                        "/",
                                        installment,
                                        ")"), 
                                        amount
))))))))
,1),3)

Also, there is a need to convert the cell ranges into variables since we are manipulating data from multiple rows. This is why there is an additional LET function assigning values to variables before applying the base formula from the previous post.

Output:

1/1/2023	Plan A (1/3)	$15,000.00
4/1/2023	Plan A (2/3)	$15,000.00
7/1/2023	Plan A (3/3)	$15,000.00
1/3/2024	Plan B (1/5)	$1,000.00
1/3/2025	Plan B (2/5)	$1,000.00
1/3/2026	Plan B (3/5)	$1,000.00
1/3/2027	Plan B (4/5)	$1,000.00
1/3/2028	Plan B (5/5)	$1,000.00

References:

• WRAPROWS
• TOROW

本文标签： google sheetsHow to Extend a Formula to Apply to All Rows Using MAP or ARRAYFORMULAStack Overflow