I want to build a array function in Excel 2019 and I want to use some array constant like {0;1;2;3;...}, this will always starting from 0 but end with different number. The number will be calculated from other cell, let's suppose say D5, How to achieve this?
Please add an example workbook so the question is clear and we dont have to recreate the data
Ok, Thanks. Please see attached file. Please focus on the formula for Calendar of June.
The array formula is using "StartDate+{0;1;..;7}*WeekDays" to calculat, I want to modify formula to 0:N11. Since sometimes N11 will be 6, 7, or 8. so that I can get a dynamic calculation.
You could use:
ROW(INDIRECT("1:"&N11+1))-1
Thanks a lot. I thought this way but just stuck by starting number with 0.
By the way, If I want to use for {1, 2, 3, ...} for this, is this same way or others?
If you want it to start at 1, just remove the -1 at the end and use N11 rather than N11+1
Thanks. So whatever will it apply for {1;2;...;n} or {1, 2, ...,n}, can use same methodology?
If you need to change orientation, you can wrap it in a TRANSPOSE function.
Thanks a lot.
