Search code examples
arraysexcelexcel-formulaexcel-lambda

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

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: correct makearray 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.

correct number

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. ongrw

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

Solution

  • 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"

    enter image description here