Ask Your Question
0

Multiplication in function returning boolean value

asked 2016-09-04 05:52:06 +0100

Guribot gravatar image

updated 2016-09-04 06:16:19 +0100

I am trying to write a function to sort through a list of abbreviated numbers using K and M, and converting these into numbers (e.g. 3.4K, .98M --> 3400, 980000). However, for some reason, when I add the multiplication to the function, instead of returning a number, it returns a boolean value.

So for instance, where A2 = 2.3K, the function

=IF(ISNUMBER(SEARCH("K",A2)), LEFT(A2,LEN(A2)-1), IF(ISNUMBER(SEARCH("M",A2)), LEFT(A2,LEN(A2)-1), "ERROR"))

returns 2.3, but the function

=IF(ISNUMBER(SEARCH("K",A2)), (LEFT(A2,LEN(A2)-1) * 1000), IF(ISNUMBER(SEARCH("M",A2)), (LEFT(A2,LEN(A2)-1) * 1000000), "ERROR"))

returns TRUE. Furthermore, the function

=LEFT(A2,LEN(A2)-1)*1000

returns 2300.

How can this be? Thanks!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-09-04 08:01:30 +0100

karolus gravatar image

Hallo

It seems you has formatted the Formulacell (accidently) to show Boolean Values.
Try: →Rightclick→remove DirectFormat

and use Formula:

=IFERROR(MID(A2,1,SEARCH("K",A2)-1)*1000,IFERROR(MID(A2,1,SEARCH("m",A2)-1)*1000000,"Error"))

edit flag offensive delete link more

Comments

Thanks so much! Such an easy fix!

Guribot gravatar imageGuribot ( 2016-09-05 08:00:43 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-09-04 05:52:06 +0100

Seen: 48 times

Last updated: Sep 04 '16