MAX function on 2 runtime-determined ranges that may contain #NV
I need some thoughts on how to improve my concept before I begin to prevent this from becoming a mile long write-only formula...
What I'm trying to do, graphically, is this:
I have two rows that have 4 mandatory cells (straight line) and 4 optional cells (dotted line) that I need to run a MAX function on. ANY number of the X's may contain #NV (for diagram purposes, these happen deliberately).
First, I need to determine the actual ranges. This is currently done with INDIRECT(..). Depending on the current quarter it selects a range of 5 to 8 cells in the rows.
INDIRECT("Q5:" & CHAR(CODE("T") + VarQuarter) & 5)
After that, MAX is performed on the range and then on the previously calculated MAX result of the two ranges. In case of an error (because of an #NV), that result needs to be omitted, otherwise both results are used. Should both results be erroneous, I'm fine with the resulting error as that one will be caught later.
My only idea for this would be endlessly long concatenations of IFERROR and redundant MAX statements...
Any ideas for improvement for any of these 2 steps? I was specifically told to perform this on the worksheet and not in code, for easier maintainability by others, so this will have to make do unless it is absolutely impossible.
Assuming the first row starts in Q5 and the second row starts in Q6 try
=MAX(IFERROR(MAX(OFFSET(Q5,0,0,1,varQuarter+4)),0),IFERROR(MAX(OFFSET(Q6,0,0,1,varQuarter+4)),0))