Getting the Average of Text from a Table with criteria

[APorter09]

I have created a template which provides recommendations, and the number of recommendations is very large. I would like to provide a summary sheet which provides an average of the text items (Red, Amber, Green) for each category. I know that you can do this via VBA, but where I am working they do not allow macro enabled excel workbooks! Therefore, i am try to find away to do this through a formula. I have looked around and cannot seem to get anything working, and would appreciate any help

I am also a fan of Excel tables / Listojects, as when used with formulas they automatically capture all the data, so any suggestions with ListObjects would be great

The detailed recommendations is something like the below:-

Category 1	C01	Red
Category 1	C01	Amber
Category 2	CO2	Red
Category 2	CO2	Red
Category 3	CO3	Green
Category 3	CO3	Amber

<tbody>
</tbody>

The summary would something like:-

Category	Average Rating
Category 1	Red
Category 2	Red
Category 3	Green

<tbody>
</tbody>

The formula would need to only look at the rows for an individual category, then do the average

Thank you all in advance

 

[FranzV]

When you say Average Rating, are you thinking about the most common value (Red, Amber or Green) for each category or is there a numeric value behind the colors that could be averaged?

(For example, if Red=1, Amber=2, and Green=3, a category with one Red recommendation and one Green would average Amber, since the average of 1 and 3 is 2)

This can totally change the approach needed to solve your scenario. I would use a PivotTable for the first approach or an AVERAGEIFS for the latter.

 

[APorter09]

Thank you FranzV. The client is now happy for macros to be embedded, therefore, i have now written some VBA which is working a treat