2

I have an excel spreadsheet containing a list of strings in one column. The list of strings is made up of several numbers from varying lengths, separated by “/” and “;” The first entry of the string is a code id (which always has a length of 3)(red in example) followed by an “/” then an amount (which varies in length)(green in example) followed by an “;” if the string continues.

With the help of a member, I can now isolate the green number with the following formula:

=IF(ISBLANK(A4);"";TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A4;"/";";");";";"</s><s>")&"</s></t>";"//s[position() mod 2 = 0]")))

However, I still need an other formula that multiplies the green number with a variable, if a condintion is met.

Example function:

=IFS(B2<=10;B2*1,25;B2<=20;B2*1,18;B2<=100;B2*1,05;B2<=250;B2*1,01;B2>250;B2)

Is there a way to combine both these functions?

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Greatly appreciated! Where did you learn most of this, if I may ask?
@Den8, you'r welcome. I learned most by just playing around with formulas for years out of interest.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.