How to concatenate two ranges?

[HIlikus]

Is there any way to concatenate two column ranges in memory (i.e. without using intermediate cells)?

The short version is i’m trying to do something analogous to =SUM(A3:A5+C7:C10) where + is the concatenation of both ranges, which I made up just as explanation

So in this case I concatenate the two ranges A3:A5 with C7:C10 which would give [A3, A4, A5, C7, C8, C9, C10]

The long version is that I am trying to calculate XIRR, which requires that the balance of the account is in the same cell range as the flows but the balance needs to be negative so I would like to concatenate the range of flows with the negative of the balance

This would allow me to do something like =XIRR(A1:A5 + (-1 * M7), C1:C5 + N7) so the first parameter would be [A1, A2, A3, A4, A5, -M7]

[mark_t]

Use the tilde “~” to concatenate two ranges, example

=SUM(A3:A5~C7:C10)

I’ve never used the XIRR function and don’t really understand from your description what you are trying to accomplish.

[HIlikus]

I didn’t know about ~, that is (I thought) exactly what I needed. It works well for SUM but it doesn’t work with XIRR. Just to clarify what I’m trying to accomplish. I need to have all cash flows and final balance in one single column where the last cell is the balance * -1, but because I store my balance somewhere else (not in the same column), i wanted to concat the values in the column with the negative balance before passing it to XIRR, as seen in the question, however it gives “Error:504”

[HIlikus]

do you have any suggestions?

[mark_t]

I think the XIRR function expects a single column of data, you could create a helper column, that would assemble the values into a single column. Maybe hide that column so it is not visible.

[mark_t]

Or you could set A6 = -M7, and then change the format to white text on white background so it is not visible, or hide row 6.

[mark_t]

Or you could set A6 = -M7, and then change the format to white text on white background so it is not visible, or hide row 6.