LinkBack URL
About LinkBacks
Sometimes the IRR or XIRR can not calculate unless the range starts at the first negative value. I need to figure out how that starting cell address, then use it in an IRR formula. I would like to avoid array formulas or VBA. Thanks for any help.
Sometimes the IRR or XIRR can not calculate unless the range starts at the first negative value. I need to figure out how that starting cell address, then use it in an IRR formula. I would like to avoid array formulas or VBA. Thanks for any help.
All I can think of at this time is to seek a value less than zero (IF statement in the next column) and mark that cell. Then look for that mark via (MATCH ) and you got the row number of the first negative the long way.
Here is a VBA solution to find the first negative number in Column A. I know that you requested a non-VBA solution, but I am just giving you choices.
Please Login or Register to view this content.
Last edited by alansidman; 10-14-2017 at 02:14 PM.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Thanks for the responses.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
If your range is A2:A10 then you can use this formula to get the position of the first negative number in that range (e.g. 1 for A2, 2 for A3 etc.)
=MATCH(1,INDEX((A2:A10<0)+0,0),0)
.....and you can use INDEX to build the range that starts at that position and ends at A10, i.e.
=INDEX(A2:A10,MATCH(1,INDEX((A2:A10<0)+0,0),0)):A10
You can use that inside other formulas that reference a range, e.g.
=IRR(INDEX(A2:A10,MATCH(1,INDEX((A2:A10<0)+0,0),0)):A10)
Audere est facere
Thanks. Great solution and now solved!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks