Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Understanding a formula
Can anyone tell me why this formula works. It was given to me as a solution
and it works I just dont understand why. I was trying to count how many charaters in a cell are letters. For example JXR1234 = 3 =SUMPRODUCT(LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8; 9},"")))-9*LEN(A22) I understand how sumproduct works, just not in this instance. Thanks for any help you can send my way. Confused in Phoenix. |
#2
|
|||
|
|||
This will not answer your question, but it is the LEN command that counts
the number of characters. Unfortunately, numbers are considered to be characters, and therefore the extra SUBSTITUTE command is necessary. I'm not quite sure about the rest of this formula, but I did find one flaw. If your entry has a space in it, it will count that as a charater. I'm sure someone else will probably post a better explanation as well as a corrected formula. HTH, Paul "Jordan" wrote in message ... Can anyone tell me why this formula works. It was given to me as a solution and it works I just dont understand why. I was trying to count how many charaters in a cell are letters. For example JXR1234 = 3 =SUMPRODUCT(LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8; 9},"")))-9*LEN(A22) I understand how sumproduct works, just not in this instance. Thanks for any help you can send my way. Confused in Phoenix. |
#3
|
|||
|
|||
If you select the SUBSTITUTE(...) part of the formula and press F9, you'll
see: {"JXR1234";"JXR234";"JXR134";"JXR124";"JXR123";"JX R1234";"JXR1234";"JXR1234" ;"JXR1234";"JXR1234"} Note the 2nd item is missing a 1, the 3rd item is missing a 2, 4th is missing a 3 and 5th is missing a 4. The rest all all there. now the LEN part returns {7;6;6;6;6;7;7;7;7;7} the SUMPRODUCT now returns 66 (add 'em up) and from this, 9*LEN(A22) or 9*7 or 63 is subtracted. So you have 66-63, or 3, the number of letters. If you change the text from JXR1234 to something else, like J1234567 & do the above steps it'll get clearer still. Nice formula, by the way! Bob Umlas Excel MVP "Jordan" wrote in message ... Can anyone tell me why this formula works. It was given to me as a solution and it works I just dont understand why. I was trying to count how many charaters in a cell are letters. For example JXR1234 = 3 =SUMPRODUCT(LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8; 9},"")))-9*LEN(A22) I understand how sumproduct works, just not in this instance. Thanks for any help you can send my way. Confused in Phoenix. |
#4
|
|||
|
|||
PCLIVE wrote...
This will not answer your question, but it is the LEN command that counts the number of characters. Unfortunately, numbers are considered to be characters, and therefore the extra SUBSTITUTE command is necessary. I'm not quite sure about the rest of this formula, but I did find one flaw. If your entry has a space in it, it will count that as a charater. .... Why shouldn't spaces be counted as characters? "Jordan" wrote in message .... For example JXR1234 = 3 =SUMPRODUCT(LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7; 8;9},"")))-9*LEN(A22) .... The array SUBSTITUTE call returns the lengths of A22 with each of the decimal numerals removed separately. Subtract each of these results from LEN(A22) and the result will be an array of the number of times each decimal numeral appears in the string. Sum that array, and the result is the total number of decimal numerals in the string. That would be =SUMPRODUCT(LEN(A22)-LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8;9},""))) Then subtract this from LEN(A22) to give the number of other characters in A22, so =LEN(A22)-SUMPRODUCT(LEN(A22)-LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8;9},""))) But addition is associative, so this could be rearranged as =LEN(A22)-10*LEN(A22) -(SUMPRODUCT(-LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8;9},""))) and this reduces to =SUMPRODUCT(LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8; 9},"")))-9*LEN(A22) If you didn't want to count spaces, just change this to =SUMPRODUCT(LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8; 9;" "},"")))-10*LEN(A22) note that the constant term in the last expression (9 or 10) is one less than the number of entries in the array constant. You could adapt this to put all characters to exclude into a string. =SUMPRODUCT(LEN(SUBSTITUTE(A22,MID("0123456789 ", ROW(INDIRECT("1:"&LEN("0123456789 "))),1),""))) -(LEN("0123456789 ")-1)*LEN(A22) |
#5
|
|||
|
|||
Don't get me wrong. The space should be counted as a "character"...but
based on the initial question, they wanted to know how many charaters in a cell are "Letters". Since a space is not a letter, and the formula is not written to omit or substitute spaces, then the result will be incorrect if referenced cell contains a space or spaces. "Harlan Grove" wrote in message oups.com... PCLIVE wrote... This will not answer your question, but it is the LEN command that counts the number of characters. Unfortunately, numbers are considered to be characters, and therefore the extra SUBSTITUTE command is necessary. I'm not quite sure about the rest of this formula, but I did find one flaw. If your entry has a space in it, it will count that as a charater. ... Why shouldn't spaces be counted as characters? "Jordan" wrote in message ... For example JXR1234 = 3 =SUMPRODUCT(LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7 ;8;9},"")))-9*LEN(A22) ... The array SUBSTITUTE call returns the lengths of A22 with each of the decimal numerals removed separately. Subtract each of these results from LEN(A22) and the result will be an array of the number of times each decimal numeral appears in the string. Sum that array, and the result is the total number of decimal numerals in the string. That would be =SUMPRODUCT(LEN(A22)-LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8;9},""))) Then subtract this from LEN(A22) to give the number of other characters in A22, so =LEN(A22)-SUMPRODUCT(LEN(A22)-LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8;9},""))) But addition is associative, so this could be rearranged as =LEN(A22)-10*LEN(A22) -(SUMPRODUCT(-LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8;9},""))) and this reduces to =SUMPRODUCT(LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8; 9},"")))-9*LEN(A22) If you didn't want to count spaces, just change this to =SUMPRODUCT(LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8; 9;" "},"")))-10*LEN(A22) note that the constant term in the last expression (9 or 10) is one less than the number of entries in the array constant. You could adapt this to put all characters to exclude into a string. =SUMPRODUCT(LEN(SUBSTITUTE(A22,MID("0123456789 ", ROW(INDIRECT("1:"&LEN("0123456789 "))),1),""))) -(LEN("0123456789 ")-1)*LEN(A22) |
#6
|
|||
|
|||
Thanks for all the help. I understand the sumproduct now and how the
substitute is working. Very Cool. I still dont get why - 9 and * the length works. Still confused in Phoenix. Thanks for all the help, I really appreciate it. "Jordan" wrote: Can anyone tell me why this formula works. It was given to me as a solution and it works I just dont understand why. I was trying to count how many charaters in a cell are letters. For example JXR1234 = 3 =SUMPRODUCT(LEN(SUBSTITUTE(A22,{0;1;2;3;4;5;6;7;8; 9},"")))-9*LEN(A22) I understand how sumproduct works, just not in this instance. Thanks for any help you can send my way. Confused in Phoenix. |
#7
|
|||
|
|||
PCLIVE wrote...
Don't get me wrong. The space should be counted as a "character"...but based on the initial question, they wanted to know how many charaters in a cell are "Letters". Since a space is not a letter, and the formula is not written to omit or substitute spaces, then the result will be incorrect if referenced cell contains a space or spaces. .... Fair point. To count only letters, use =26*LEN(A22)-SUMPRODUCT(LEN(SUBSTITUTE(UPPER(A22), MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",ROW(INDIRECT("1:2 6")),1),""))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i write a formula and keep in in formula form, so it DOESN. | Excel Discussion (Misc queries) | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Understanding net present value formula in Excel | Excel Worksheet Functions | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Cell doesn't show formula result - it shows formula (CTRL + ' doe. | Excel Worksheet Functions |