Hi, Is there some way wherein I can use the XNPV function as a general formula for a set of cells? To get the value of XNPV, the selected cells should only be the ones with values in it. But the other cells are cash flows which changes whenever I change the date, so I have to move the selected cells again to get the value. Is there some way where the formula ignores the other selected blank cells? Thanks so much!excel.png
I'm not familiar with XNPV, but on testing it has to have a date in every cell of the selected date range to work. I suspect there is a more efficient way of doing this, but the following formula will work by dynamically changing the ranges used by XNPV according to the contents of column B:
=XNPV(C16,INDIRECT(ADDRESS(20,3)&":"&ADDRESS(ROW(B119)-COUNTIF(B20:B119,"-"),3)),INDIRECT(ADDRESS(20,2)&":"&ADDRESS(ROW(B119)-COUNTIF(B20:B119,"-"),2)))
This assumes that your formulas in column B to bring through the date are setup to show a "-" if no date is bring through.
Last edited by tricky01; 01-07-2015 at 05:35 AM.
It worked! I've been thinking about this on how to make it work for days now. Thanks so much for your response. I really appreciate it!
Glad to have been able to help. The more time I spend on here though the more I expect there's a better more efficient way to do it - but at least that works [Originally Posted by fitrader
]
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks