Posted to microsoft.public.excel.worksheet.functions
|
|
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
|