Re: Decipher Formula

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