Simple Excel formula
 

Hi Reader


Simple Excel formula, How to set the formula ?
I try =IF(SEARCH("K",C2,1) 0,1000,IF(SEARCH("M",C2,1)1,1000^2,1))
not work

Check B in 536.0B return 1
Check k in 14.1k return 1000
Check M in 41.1B return 1000000
Check G in 1.2G return 1000000000

Simple Excel formula
 

The problem is that SEARCH does not return 0 when it finds nothing, it
returns #VALUE. Try

=IF(ISNUMBER(SEARCH("K",C2,1)),1000,IF(ISNUMBER(SE ARCH("M",C2,1)),1000^2,1))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"moonhk" wrote in message
...
Hi Reader


Simple Excel formula, How to set the formula ?
I try =IF(SEARCH("K",C2,1) 0,1000,IF(SEARCH("M",C2,1)1,1000^2,1))
not work

Check B in 536.0B return 1
Check k in 14.1k return 1000
Check M in 41.1B return 1000000
Check G in 1.2G return 1000000000

Simple Excel formula
 

Hi,

You can use a Lookup() function :

=LOOKUP(RIGHT(A1,1),{"B","G","K","M"},{1,100000000 0,1000,1000000})

HTH