Need help with frequency formula
On Fri, 6 Feb 2009 23:49:01 -0800, ISAF Media Analysis
wrote:
Does anyone know how to determine the most used words of numbers within a
given range. For example, I am trying to figure out the following:
A B C D F
1 dog Cat Snake Pig positive
2 Cat Snake Horse Blue positive
3 light bulb Cat foot negative
4 kid school hat Cat negative
I need a formula that would count the words in columns "A" thru "D" for
example but would only return the words based on the value of column "F,"
(i.e. -- how many times the word "Cat" appears in A:D, but negative in F.
Any help would be greatly apprieceated. Thanks in advance.
Try this formula:
=SUM(MMULT(TRANSPOSE(--(A1:D100="Cat")),(--(F1:F100="negative"))))
Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER
Change the 100 in both places to fit the size of your data.
Hope this helps / Lars-Åke
|