What would I put into the formula bar to get the hardcoded RRI column on the far right?
What would I put into the formula bar to get the hardcoded RRI column on the far right?
In the future, please follow the instructions at the top of this webpage to attach an example file, or at least post the data in a form that is easy to copy-and-paste into Excel.
For the example below:
rri example.jpg
the formula in O3 is:
=RRI(COUNTIF(B3:M3,">0")-1, B3, IFERROR(INDEX(B3:M3, MATCH(0, B3:M3, 0)-1), M3))
That assumes that any zeros are always at the end of the series.
In effect, O3 is =RRI(COLUMNS(C3:M3), B3, M3), which is equivalent to
=RATE(COLUMNS(C3:M3), 0, -B3, M3) or =(M3/B3)^(1/(COLUMNS(C3:M3)) - 1.
O4 is =RRI(COLUMNS(C3:G3), B3, G3).
PS.... The formula can be simplified if you leave cells empty instead of entering zeros.
PPS.... Minor simplifications. If they are confusing, stick with the original formula.
=RRI(COUNTIF(C3:M3,">0"), B3, IFERROR(INDEX(C3:M3, MATCH(0, D3:M3, 0)), M3))
-----
Last edited by curiouscat408; 03-01-2023 at 05:36 PM.
Thank you so much. I will keep that in mind for next time I post!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks