Counting The Occurances Of A Frequency or Mode

[fla5hbak]

Hi


Found these boards after scratching my head over a Excel sheet for a few hours. I hope you can help me. I have been searching through the messages but cannot seem to find the required information.

I have a spreadsheet, on which there is a presentation page. This presentation page is to be automatically updated using formulas to reference other pages within the workbook.

So, I want to find the frequency of a number that is based on two criteria, "ctype" and "group". The frequency bit is called "setting", and all of these are numerical data.

I want to cross reference the "group" and "ctype" data to find which number occurs most frequently in the "setting" column, then count how many instances of that number exist within that cross-reference.

I figure this can be done using the CSE type method, but I cannot figure out the syntax of executing it.

Any help would be appreciated. Thanks


Trefor

 

[fairwinds]

Welcome to the board!

Try:
F4: =MODE(IF((A2:A10=E1)*(B2:B10=E2),C2:C10))
confirmed with Ctrl + shift + enter.


F5: =SUMPRODUCT(--(A2:A10=E1),--(B2:B10=E2),--(C2:C10=F4))
normal entered

Book2.xls
	A	B	C	D	E	F
1	Type	Group	Setting		aaa
2	aaa	xxx	5		xxx
3	aaa	yyy	7
4	aaa	xxx	5		Mode	5
5	aaa	yyy	7		Occurances	2
6	aaa	xxx	7
7	aaa	yyy	7
8	aaa	xxx	7
9	bbb	yyy	7
10	bbb	xxx	7
Sheet4

 

[fla5hbak]

Er. I wasnt expecting THAT quicka reply. Thanks!

I have altered the formula you provided to read:

=MODE(IF((ctype=1)*(group=1),setting))

as I have used column naming to make things a bit easier on the eyes.

Now, it half worked, as it returned a value of 4, which is the mode of selected and filtered data. What I need now is to count how many times that number occurs within the "setting" column, while still meeting the "ctype" and "group" criteria.

=)


Can you provide any further insight?

 

[fla5hbak]

Oh crap. Sorry. Didnt read the last bit of your post! *SLAPS FOREHEAD*

Will give it a whirl now. Thanks again. =)

 

[fairwinds]

Check out the SUMPRODUCT formula I posted above.



EDIT: So you did.