Categories
User Help

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

Please refer to the screenshot:

screenshot

Leave a Reply

Your email address will not be published. Required fields are marked *