How to concatenate two ranges?

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]

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.

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”

do you have any suggestions?

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.

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.

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.