Re: Decipher Formula

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 12/03/04 

Date: Fri, 3 Dec 2004 19:08:03 +0100

Hi
The SUMPRODUCT part:
SUMPRODUCT(LEN(J3:P57)-LEN(SUBSTITUTE(J3:P57,"Discard","")))

calculates for each cell in the range J3:P57 the difference between the
original string
LEN(J3:P57)
and the string which results if you substitute 'Discard' in these
strings with nothing ""
LEN(SUBSTITUTE(J3:P57,"Discard",""))

These results are then summed. So lets consider you have the following
three strings:
J3: This is a normal string
J4: With Discard
J5: Discard and another Discard

The SUMPRODUCT formula would return the following for these three
cells:
J3: LEN("This is a normal string")-LEN("This is a normal string") = 0
J4: LEN("With Discard")-LEN("With ") = 7
J5: LEN("Discard and another Discard")-LEN(" and another ") = 14

Then it sumes the results and in our example you'll get 21
This is then divided by the length of 'discard' = 7 and the final
result would be 3

So in total this formula counts the number of occurences of the string
'Discard' within the specified range 

--
Regards
Frank Kabel
Frankfurt, Germany
"Paula" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag
news:0f8e01c4d961$a3ec1630$a601280a@phx.gbl...
> Would someone please decipher this formula for me in
> layman's term?
>
> =SUMPRODUCT(LEN(J3:P57)-LEN(SUBSTITUTE
> (J3:P57,"Discard","")))/LEN("Discard")
>
> Thank you

Relevant Pages

  • Re: Parsing JSON (#155)
    ... In some places where I don't want to discard I use (? ... converting it to Ruby 1.8 like this: ... def invalid ... 'Invalid JSON: %s' % string ...
    (comp.lang.ruby)
  • Re: reading scientific notation numbers from string
    ... > I am trying to read some numbers that are in scientific notation out of> a string. ... you scan up to and including the = and discard that part. ... as literal characters are automatically discarded when matched. ...
    (comp.soft-sys.matlab)
  • does "block: add secure discard" cause an overflow?
    ... This is the commit 8d57a98ccd0b44 "block: add secure discard". ... The patch adds a letter 'E' to the string in blk_fill_rwbs. ...
    (Linux-Kernel)
  • Re: How to use EOF in C
    ... nch = nch + 1; ... So if we get EOF, we simply discard the data read to that point, ... An '\0' by itself is not a string. ... If you have a character pointer pointing to that then the pointer is ...
    (comp.lang.c)
  • Re: How to count the number of times a string appears in a column?
    ... Assuming that the string you want to count is in C1, ... Note that SUMPRODUCT doesn't work with complete columns, ... and each cell contains up to 10 or 15 strings. ... > string appears in the column, which exact one appears most, etc etc. ...
    (microsoft.public.excel.worksheet.functions)