Sum of individual digits (just for fun)
I want to sum how often a certain digit (for example 1) appear in
the in the sequence of whole numbers 1, 2, ,...., N N 1 10 11 21 .... f(N) 1 2 4 13 .... N=21 --- f(N)= number of ones in {1, 10, 11, 12, 13, 14 ,15 ,16, 17 ,18, 19, 21}=13 I have mmade an attempt with the following array formula: =CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A11))),1)) which for N=1 returns 49, for N=10 returns {49,48} and so on. How can I extend above formula (count the "49's") so that I get the results shown under f(N) or maybe a better way? Hans Knudsen |
Sum of individual digits (just for fun)
Hi Hans
Try this for every 1 in A1:A10 =SUMPRODUCT(--(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"1","")))) -- Regards Ron de Bruin http://www.rondebruin.nl "Hans Knudsen" wrote in message ... I want to sum how often a certain digit (for example 1) appear in the in the sequence of whole numbers 1, 2, ,...., N N 1 10 11 21 .... f(N) 1 2 4 13 .... N=21 --- f(N)= number of ones in {1, 10, 11, 12, 13, 14 ,15 ,16, 17 ,18, 19, 21}=13 I have mmade an attempt with the following array formula: =CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A11))),1)) which for N=1 returns 49, for N=10 returns {49,48} and so on. How can I extend above formula (count the "49's") so that I get the results shown under f(N) or maybe a better way? Hans Knudsen |
Sum of individual digits (just for fun)
Hi Ron
Of course. How simple can it be! Thank you very much. Regards Hans "Ron de Bruin" skrev i en meddelelse ... Hi Hans Try this for every 1 in A1:A10 =SUMPRODUCT(--(LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,"1","")))) -- Regards Ron de Bruin http://www.rondebruin.nl "Hans Knudsen" wrote in message ... I want to sum how often a certain digit (for example 1) appear in the in the sequence of whole numbers 1, 2, ,...., N N 1 10 11 21 .... f(N) 1 2 4 13 .... N=21 --- f(N)= number of ones in {1, 10, 11, 12, 13, 14 ,15 ,16, 17 ,18, 19, 21}=13 I have mmade an attempt with the following array formula: =CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A11))),1)) which for N=1 returns 49, for N=10 returns {49,48} and so on. How can I extend above formula (count the "49's") so that I get the results shown under f(N) or maybe a better way? Hans Knudsen |
All times are GMT +1. The time now is 03:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com