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