Excel – Add an excel formula within FilterXML

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)),"")

enter image description here

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.

Leave a Comment

Your email address will not be published.

Scroll to Top