MODE function

hi all, ive a problem using MODE function.

im using Excel 2007 and got no problem before.

example, i have a bunch of phone numbers let say:

[B2]
1234567
8856941
3571592
3625148
8871567
1679248
3397415
6546546

then im using MODE function for first digits. [N2] =MODE(B2:B999)

it will come out like this =

[N2]
1
8
3
3
8
1
3
6

after that, i group first two digits of each numbers. [G2] =CONCATENATE(B2,C2)
its come out like this:

[G2]
12
88
35
36
88
16
33
65

then ive got an error when using MODE function for those two digits. [N5] =MODE(G2:G999)

[N5]
#N/A

can anyone tell what i did wrong?

*im sorry for my english but i hope you guys can understand me.

Solution

Hello,

The reason you are receiving an error is because when you use "=CONCATENATE(B2,C2)" It is converting the values original data type from Type1 toType2 (which Type2 considered text and Type1 a number value). You can check your data type using the "=Type(Cell)" Formula. Similar errors can happen with Vlookups if your types do not match (But I digress)

To fix your problem try this in G2: =CONCATENATE(B2,C2)+0

That will convert your concatenated text values into numbers. Note: Changing the cell format from text to numbers will not fix your problem, but adding the 0 forces excel to change the data type.

Also keep in mind that if you do not have any numbers that repeat in column G, then your mode function in column N will return #N/A

Hope this helps!

Sincerely,

-Max

dear Max, yes..it is helps me

dear Max,
yes..it is helps me and now its shows a value like its suppose to do. Today ive learn something new from you.

thanks from your guidance, may God bless you.

Thanks again (y).