Sum By Quarters|General Excel Questions & Answers|Excel Forum|My Online Training Hub

https://www.myonlinetraininghu.....o-quarters

Above is the link to reference article about which I have some questions that I am posting below.

SumQtrsSergei.xlsx

THUNKS

1] For multi-row and multi-column input to INDEX function, giving row no and column no as input will output single cell value. For input of column no as zero, entire row will be given as output. For single column and multi-row input, giving column no as either 0 or 1 or not giving any column no does not matters. It will give output value corresponding to the row no. Despite sumsArray being a single column and multi-row array of error cells, giving 0 or not giving column no to INDEX function throws #VALUE! error as output. Column no input must be 1. Why is that?

2] INDEX (sumsArray, 4, 1)(). INDEX function extracts cell values only after placing empty bracket in the syntax not otherwise. This is very bizarre. What’s the logic behind this?

3] sumsArray:

a) Single LAMBDA throws error in single cell however it is still holding all the values with that single cell of error.

b) Double LAMBDA throws error in multi-row single column. No of rows = No of rows of data. Each error cell is now holding data/values of i] Corresponding to that row no and column no = 1 and ii] Entire row.

c) Why second or inner LAMBDA has no declaration of variable v inside it?

d) sumsArray results into a column of error cells. How can a formula extract a non-error output of error cells or using error cells as input?

4] sumsB: Declaration of variable v inside second or inner LAMBDA of sumsArray throws error output.

Double LAMBDA + No () for INDEX: Gives column of #CALC! error but each of these error cells either 1) Do not pack the cell values of the row that corresponds to the respective error cell of the column or 2) They do pack or hold them but does not reveal them that is output them after giving input of row and column no of a cell in the array to INDEX function.

Double LAMBDA + () in the INDEX: It reveals/outputs cell value of the cell in the grid/array after giving input of row and column no of desired cell to INDEX function but there is a prerequisite that you must first input (n, 1) [where n = row no] to INDEX function so that column no becomes 1 which represents column no of error cell (CEC) output of Double LAMBDA formula. So you must give column no = 1 first and then row no as n to select corresponding error cell of CEC which packs/holds all cells with their values in that row. Then next after this one row output, you input (1, m) to second INDEX function to select that row with row no = 1 that is the row itself (Error cell with row no n from CEC) and m = column no of cell to select cell in the row whose value you want by giving its column no m.

-----------------------------------------------------------------------------------------------------------------------------------

MAKEARRAY1

If MMULT (data, --(TRANSPOSE(MonthsInQuarters)=quarters)) can deliver quarterly sum for each row then why you need MAKEARRAY, LAMBDA, INDEX, BYROW and all that fuss to get this?

-----------------------------------------------------------------------------------------------------------------------------------

MAKEARRAY2

If we include a cell address of cell containing text in sum function along with cell address of other cells containing numbers then it delivers the sum of all numbers without giving any error by ignoring cells which contain text. Cell address of cell containing text can be either specified individually or be included in a range of cells containing numbers. If on the other hand if we add a text in the function like sum(“TEXT”, A1:A5) where A1:A5 contain numbers then the function throws error. Why it doesn’t throw error if we input text value through cell address instead of directly as text string in double quotation?

==================================================================

SumQtrsPeter.xlsx

EXPAND function turns blank into 0 when delivering output directly in the cells on sheet whereas keeping blank as blank when delivering output to another function. Why is this difference?

==================================================================

It will be very nice if someone can answer atleast some of these questions. I have attached the files. These are not the original files. These files contains each step that I have unfolded to understand how each of these formulas work.