Excel combine multiple cells into one array as a variable input in a formula

Question

Have a simple excel question. I'm trying to use a formula (the IRR) which accepts an array of cells as an input, but the array I want to feed it is not lined up consecutively in one neat column. So basically what I want to do is something like this:

IRR((C15, D20:D30), 0.0005)

Where the array I want evaluated is a union between cell C15 and the array of values that exist between cells D20 and cells D30.

So if cell C15 is -100. And cells D20:D30 all have the value of 15 let's say....the array I want evaluated is:

{-100, 15, 15, 15, 15, 15, 15, 15, 15, 15, 15}

I haven't been able to figure out how to do this, is there a way to do this? I thought my syntax might work, I tried it with curly braces as well to trigger excel's built in array formulas to no avail.

This is NOT in VBA, but just in the regular old excel formula bar. Is there a way to do this? Or does this have to be done in VBA basically?

Answer 1

Did you actually try the solution you suggested? It works perfectly fine for me:

=IRR((C15,D20:D30),0.0005)

(In the picture, consider I have European settings, so replace ; with , and , with .).

You can select multiple cells by holding the CTRL key and clicking or dragging the cells.