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

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

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