Makearray function in Office Excel unable to generate proper amount of columns for the array

Question

I am using Office 365 currently and I want to make a visualization tools using MAKEARRAY functions.

For example, if I want to display sequential of 32 items, I would display it in this way: I use the following formula of Makearray to generate the custom array for me Note: Formula is pasted at cell value F3 .

=MAKEARRAY(ROUNDUP(B2/B3,0),IF(E3#=ROUNDUP(B2/B3,0),MOD(B2,B3),B3),LAMBDA(row,col,"O"))

but it seems like after debugging, this part of the formula are giving it the problem are these

IF(E3#=ROUNDUP(B2/B3,0),MOD(B2,B3),B3)

as I debugging the formula separately as shown in picture below, it can generate the correct amount of columns as it is supposed to.
Note: Generate exactly same amount to the no of columns if row number is not matching;
Generate modulus remainder formula if row number is matching to roundup of no. of items divided by no. of columns.

But in the end, I put that problematic formula back into the MAKEARRAY function just give only a single columns, which seems like it is quite wrong.

May I know why it display single columns even though by right, it should display the correct amount of no. of columns?

JvdV · Accepted Answer · 2022-09-20 16:15:52Z

4

What about:

Formula in C1:

=WRAPROWS(INDEX("O",SEQUENCE(A1,,,0)),A2,"")

Or rather:

=WRAPROWS(EXPAND("O",A1,,"O"),A2,"")

edited Sep 20, 2022 at 16:15

answered Sep 20, 2022 at 15:45

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

P.b Over a year ago

I didn't even know WRAPROWS existed. Nice use of it. I would've used SEQUENCE: =IF(SEQUENCE(ROUNDUP(B2/B3,0),B3)<=B2,"O","")

Scott Craner · Accepted Answer · 2022-09-20 15:39:38Z

3

MAKEARRAY does not expect an array in the number of columns. It is a set number. It will iterate the number of rows and number of columns to create the array. It will always be square and not jagged.

So you need to do the math to change the value:

=MAKEARRAY(ROUNDUP(B2/B3,0),B3,LAMBDA(rw,clm,IF(10*(rw-1)+clm>B2,"","O")))

Now as soon as the space is greater than the 32 it puts in "" instead of "O"

answered Sep 20, 2022 at 15:39

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

1 Comment

Alan Koh W.T Over a year ago

This really helps to solve the problem although I have slight modifications to the formula: I change 10 to B3 to make it more dynamic. =MAKEARRAY(ROUNDUP(B2/B3,0),B3,LAMBDA(rw,clm,IF(B3*(rw-1)+clm>B2,"","O")))

Collectives™ on Stack Overflow

Makearray function in Office Excel unable to generate proper amount of columns for the array

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related