Re: formula to count charcter in a range

Thanks for the analysis. I assume that for this part...

Your formula as you wrote it could effectively require
6 iterations through range: once for "*w*", effectively
2 for "*w*w*", and effectively 3 for "*w*w*w*".

you use of the word "effectively" is meant to cover the fact that, for example, in "*w*w*", once one 'w' is found, the search loop must continue on in order to look for the next 'w'.

--
Rick (MVP - Excel)


"Harlan Grove" <hrlngrv@xxxxxxxxx> wrote in message news:2dafe610-bb2d-4bbd-99d1-913e2dee0064@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
"Rick Rothstein" <rick.newsNO.S...@xxxxxxxxxxxxxxxxxx> wrote...
...

Changing arguments for clarity.

=SUMPRODUCT(LEN(range)-LEN(SUBSTITUTE(LOWER(range),LOWER(char),"")))

How would this formula compare efficiency-wise?

=SUMPRODUCT(COUNTIF(range,"*"&REPT(char&"*",instance_array)))
...

My formula would loop through range 5 times: the first LEN call, the
LOWER call, the SUBSTITITE call, the second LEN call, and the final
SUMPRODUCT call. However, my SUBSTITUTE call would only iterate
through range once, and perform a simple operation - effectively
deleting all w's.

Your formula would iterate through range as many times as there are
entries in instance array for the COUNTIF call, in your example 3
times. However, processing a range with COUNTIF where the second
argument contains wildcards isn't as simple as my SUBSTITUTE call. If
COUNTIF special cases patterns in which the first, last or both chars
are *, then COUNTIF should process the pattern "*w*" at least as fast
as SUBSTITUTE(range,"w","",1) would, and probably faster. However,
it's unlikely COUNTIF special cases patterns with *'s between literal
characters. When those occur, you need significantly more logic FOR
EACH entry in range, approximating an inner loop.

Your formula as you wrote it could effectively require 6 iterations
through range: once for "*w*", effectively 2 for "*w*w*", and
effectively 3 for "*w*w*w*". That is, if R were the number of entries
in range, A were the number of sequential entries in instance_array,
then my formula would be O(N) with a large constant (c), but yours
would be O(N A^2) with a small constant (d). As long as c > d A^2,
yours would be faster. But as soon as c < d A^2, mine would be faster.
I'd guess yours would always be faster for A = 2, usually faster for A
= 3 except when most entries in range have at least 2 w's, and seldom
if ever faster for A >= 4.

.

Relevant Pages

  • Re: formula to count charcter in a range
    ... Your formula would iterate through range as many times as there are ... argument contains wildcards isn't as simple as my SUBSTITUTE call. ... EACH entry in range, approximating an inner loop. ... in range, A were the number of sequential entries in instance_array, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Can this be vectorised?
    ... DaveW wrote: ... the same number of entries as Y and each element the result of a ... allows me to iterate on each y in Y without using a for loop? ...
    (comp.soft-sys.matlab)
  • Re: Can this be vectorised?
    ... DaveW wrote: ... the same number of entries as Y and each element the result of a ... allows me to iterate on each y in Y without using a for loop? ...
    (comp.soft-sys.matlab)
  • Can this be vectorised?
    ... the same number of entries as Y and each element the result of a ... Is there a syntax that ... allows me to iterate on each y in Y without using a for loop? ...
    (comp.soft-sys.matlab)
  • RE: Creating a que
    ... and entry count in a collection. ... if eo.Count> 0 then bMoreData = True ... At the beginning of the loop you assume this is the last time through the ... Then you go through each item in the Entries ...
    (microsoft.public.excel.programming)
Loading