# XIRR with potentially blank cells in the range

#### Nixhex11

##### Board Regular
Guys,
I've built a financial model for projects that range in length and thus have had to build in the flexibility to handle this. In the dates portion of my cash flow within the model, I have formulas dragged over for 16 years max but the cells will only populate if the project is ongoing and will be blank if it is over (depending on user inputs).
I'd like to have an XIRR calculation built in but I get an #NUM! error due to my blank cells. I will need to cover the entire range since it's impossible to predict how long each project will be. Is there a formula that detects the range of <>"" cells and then a way to do an xirr calculation on just those? Any help would be appreciated!

Thanks!

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Figured it out - named both ranges (cash flows and dates) using =offset(origin cell, 0,0, 1, count(range)). Then the formula was =xirr(cashflows, dates).

Replies
2
Views
485
Replies
2
Views
3K
Replies
0
Views
616
Replies
4
Views
2K
Replies
3
Views
1K

1,196,239
Messages
6,014,166
Members
441,807
Latest member
sjkenjalo

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back