Hi there,
I was hoping someone can assist me in crafting a formula that would look at text and return the most frequent appearing text provided it meets certain criteria.
Example data set:
[TABLE="width: 210"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Category[/TD]
[TD]Sub-category[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Breakfast[/TD]
[TD]Light[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Breakfast[/TD]
[TD]Light[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Breakfast[/TD]
[TD]Medium[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Breakfast[/TD]
[TD]Heavy[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Breakfast[/TD]
[TD]Heavy[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Lunch[/TD]
[TD]Light[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Lunch[/TD]
[TD]Light[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Lunch[/TD]
[TD]Medium[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Lunch[/TD]
[TD]Medium[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Dinner[/TD]
[TD]Light[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Dinner[/TD]
[TD]Light[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Dinner[/TD]
[TD]Medium[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Dinner[/TD]
[TD]Medium[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Dinner[/TD]
[TD]Heavy[/TD]
[TD]Good[/TD]
[/TR]
</tbody>[/TABLE]
Objective:
Provided a specific category AND subcategory, what is the most common (MODE) text in column C ("Data").
For example, For Breakfast -> Light -> the most common data point is "Good".
Thank you!
I was hoping someone can assist me in crafting a formula that would look at text and return the most frequent appearing text provided it meets certain criteria.
Example data set:
[TABLE="width: 210"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Category[/TD]
[TD]Sub-category[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Breakfast[/TD]
[TD]Light[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Breakfast[/TD]
[TD]Light[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Breakfast[/TD]
[TD]Medium[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Breakfast[/TD]
[TD]Heavy[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Breakfast[/TD]
[TD]Heavy[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Lunch[/TD]
[TD]Light[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Lunch[/TD]
[TD]Light[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Lunch[/TD]
[TD]Medium[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Lunch[/TD]
[TD]Medium[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Dinner[/TD]
[TD]Light[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Dinner[/TD]
[TD]Light[/TD]
[TD]Bad[/TD]
[/TR]
[TR]
[TD]Dinner[/TD]
[TD]Medium[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Dinner[/TD]
[TD]Medium[/TD]
[TD]Good[/TD]
[/TR]
[TR]
[TD]Dinner[/TD]
[TD]Heavy[/TD]
[TD]Good[/TD]
[/TR]
</tbody>[/TABLE]
Objective:
Provided a specific category AND subcategory, what is the most common (MODE) text in column C ("Data").
For example, For Breakfast -> Light -> the most common data point is "Good".
Thank you!