IFS function in excel not able to work

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)`
``````

• Hi @sam , your formula returns #Value error, because every time it encounters with first logical test,, and fails to read the second logical test,,, this can be handled with trapping an ERROR,, like this with `IF`,,,, `=IFERROR(IF(FIND("M",A1),SUBSTITUTE(A1,"M","")*1000000),IF(FIND("K",A1),SUBSTITUTE(A1,"K","")*1000,0))`,,, here `IF(FIND("K",A1),,,` is, Test if Error ☺ – Rajesh S Jun 2 '20 at 6:55

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.

• Thanks a lot Justin! – sam Jun 2 '20 at 2:52

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))
``````
• Thanks a lot ruud! – sam Jun 2 '20 at 2:53
• I have tried replacing "ISNUMBER" with "ISERROR", seems to be returning an error. The formulae is this. Why is it so? `=IF(ISERROR(FIND("K",F2)),SUBSTITUTE(F2,"K","")*1000,IF (ISERROR(FIND("M",F2)),SUBSTITUTE(F2,"M","")*1000000, F2))` – sam Jun 2 '20 at 3:59

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})
``````
• Thanks a lot Owen! – sam Jun 2 '20 at 2:53