A very nice way of dealing with this is assigning the full array to a name, let’s say a variable, through LET()
. So your formula in B2
would become:
=IF(A2<>"",LET(MNT,TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A2,"/",";"),";","</s><s>")&"</s></t>","//s[position() mod 2 = 0]")),IFS(MNT<=10,MNT*1.25,MNT<=20,MNT*1.18,MNT<=100,MNT*1.05,MNT<=250,MNT*1.01,MNT>250,MNT)),"")
It, however, does require Excel O365. But since you are transposing the array it appears you do have that.
CLICK HERE to find out more related problems solutions.