IFS function in excel not able to work

Question

I am not sure what is the problem, either is syntax error, or data error.

The data in 1st column F is all text. Some are in millions (with the word 'M' at the end) and some in thousands ((with the word 'K' at the end).

I want to convert all to numbers and put in column 'O'

First, I compare whether the end of the text is either 'M' or 'K' using FIND(), then i used SUBSTITUTE to remove the 'M' or 'K', and multiply by 1,000,000 or 1,000. And i put it in IFS function.

The formulae seems to work for the texts that contain 'M', but not working for 'K'. I am unable to see why. My formulae for the first column as follows:

`=IFS(FIND("M",F2)>0,SUBSTITUTE(F2,"M","")*1000000,FIND("K",F2)>0,SUBSTITUTE(F2,"K","")*1000)`

Please refer to the screenshot:

Answer 1

The formula is not able to move past the error if M is not found, you need to catch the error, if they are the only two values (K and M) that are going to show use something like:

=IFS(ISERROR(FIND("M",F2)),SUBSTITUTE(F2,"K","")*1000,ISERROR(FIND("K",F2)),SUBSTITUTE(F2,"M","")*1000000)

otherwise next another if function to return a value if M or K not present.

Answer 2

You have to specify that the cell must shown the unchanged value if K and M are not present.

I prefer IF over IFS. Always nasty problems popping up with IFS. Try

=IF(ISNUMBER(FIND("K",F2)),SUBSTITUTE(F2,"K","")*1000,IF (ISNUMBER(FIND("M",F2)),SUBSTITUTE(F2,"M","")*1000000, F2))

Answer 3

If you have a later version of Excel that supports dynamic arrays, you can use a slightly different and (in my opinion) simpler to manage approach:

I've included additional columns to show the progress towards the green solution.

First, get the scale indicator (K or M or whatever). Then use a fixed array inside XLOOKUP to translate that into a multiplier. Then, we just need to get the number in the original cell and multiply it by the multiplier.

This is the formula in the green cell E3:

=NUMBERVALUE(LEFT($A3:$A6,LEN($A3:$A6)-1))*XLOOKUP(RIGHT($A3:$A6,1),{"M","K"},{1000000,1000})