Adjustable array formula

[Median]

Hi guys,

Currently, I have a number of formulas that work correctly & deliver the results I require, however, I need their "array calculation ranges" to change depending on certain cell values. & basically I'm completely stuck!!

I've cut my data down to just a working example of the formulas, & an anticipated result.

Rows 2 to 3 are the example of the formulas working perfectly - but the array has to be constant

Rows 5 & 6 are the example of the dream formula, which could read M6 & N6, then adjust the calculation range (i.e. adjust the array) - therefore returning the values in O6, P6, Q6.

Hope I've been clear there. Any questions ask away

Cheers
Mdn

[63falcondude]

I don't understand.

If you copy and paste A6:N6 into A3:N3, the formulas that are in O3:Q3 give the desired results that are displayed in O6:Q6.

[Median]

Thought you'd cracked it Falcon (i.e. my stupidity!!)

However, as I was checking it I noticed certain combinations do break it

I've added a new workbook below. G6:I6 shoudl exactly match with O3:Q3, for some reason they don't - even though the formulas are exactly the same

[63falcondude]

I don't know what your formulas are supposed to be doing but I do know that there is nothing in the P3 (or H6) formula to limit the top end of the range.

Try including this in P3:

=IFERROR(IF(AND(INDEX(A3:L3,$M$3)>O3,SUMPRODUCT((A$2:L$2>=$M$3+1)*(A$2:L$2<=$N$3)*(A3:L3<=O3))),IF((INDEX(A3:L3,1,$N$3))<O3,"W","L"),""),"")


To compare, that would be this in H6:

=IFERROR(IF(AND(INDEX(A6:D6,$E$6)>G6,SUMPRODUCT((A$5:D$5>=$E$6+1)*(A$5:D$5<=$F$6)*(A6:D6<=G6))),IF((INDEX(A6:D6,1,$F$6))<G6,"W","L"),""),"")

[Median]

Falcon, your turning into a living legend I think you've done it.

Massively appreciated

[63falcondude]

You're very welcome. Thanks for the rep!