How do I mix Excel's dynamic array syntax with normal row references in the same formulae?

Question

How do you mix Excel's dynamic array formulae with normal row formulae, e.g in this formula: =IF(tblAnalysis[Type]="sell",tblAnalysis[Price]-MIN(Y2#,tblAnalysis[Price2]),MAX(Y2#,tblAnalysis[Price2])-tblAnalysis[Price])*10000

I am using a MAX and MIN, but I want those formulae to apply to the value in cell Y2 and the value from tblAnalysis[Price2] for that row and apply relatively for each row, in other words it should take the miminum of 2 values; but instead it calculates the minimum of the Y column and the table column Price2, for each row containing this formula.

I have tried this version also, but this does not give the right results either

=IF(tblAnalysis[Type]="sell",tblAnalysis[Price]-MIN(Y2,tblAnalysis[@Price2]),MAX(Y2,tblAnalysis[@Price2])-tblAnalysis[@Price2])*10000

Answer 1

IF Formula Example When Mixing a Spilled Array and Structured References

• It is assumed that the spilled array (column Y) is in one column and has the same number of rows as the number of data rows in the table.

=MAP(tblAnalysis[Type],tblAnalysis[Price],tblAnalysis[Price2],Y2#,
LAMBDA(a,b,c,d,IF(a="Sell",b-MIN(c,d),MAX(c,d)-b)*10000))