Return rightmost non-blank cell value in a row?
I created a more roundabout solution for this involving a separate grid, but I'm wondering if there is an elegant formula that would do the following.
See first screenshot below. Without getting into too much detail, I am hoping for a formula to put into [2022 YTD]13 that would look at the values in [Q1 2022]13:[Q4 2022]13, and return the rightmost, non-blank value (i.e. the most recent value).
Normally I would have the LARGE function pick the largest value, since the value of most of such data I collect increases in number over time. However, since the numbers reported here will be percentages, it is entirely possible that the most recent number could decrease from the previous quarter, so LARGE is not an option.
I was able to do this using a separate grid that put everything into a column/vertical format, then using actual calendar dates, MAX(COLLECT) and INDEX(COLLECT) functions, and using a lot of cell linking to get the result I wanted then return that value to [2022 YTD]13. The two next screenshots show those formulas on that separate grid:
But there has to be a more elegant solution that keeps everything to one row in one grid, as specified at the beginning. Any ideas? Or is this simply not possible?
Thanks in advance!
Help Article Resources
Check out the Formula Handbook template!