Duplicate max values in one cell required

[Caren]

Hello
I need to return (potentially) duplicate values in one cell.

My spreadsheet requires users to select a response in column C of A,B or C
(from a drop down list).
I have 3 formulas (hidden) counting the number of times a selection is made
and one which returns the maximum from those 3 formulae.ie:
=COUNTIF(C5:C65,"A") (this is in cell C81)
=COUNTIF(C5:C65,"B") " C82
=COUNTIF(C5:C65,"C") " C83
=MAX(C81:C83)

In cell C71 is a results box with this formulae:
=IF(($C$84=$C$81),"A",IF(($C$84=$C$82),"B",IF(($C$84=$C$83),"C","")))
This obviously returns a text value based on the maximum times a response was
selected.

My question is - there are 13 questions and 3 responses per question, I need
to know which is the most selected response (which I have managed), However,
there may be a 'tie' as two options may come out equally eg, A has 3
responses, B and C have 5 responses each.

I need to show in the results box 'B&C'

Thank you in advance
Caren.

 

[Bernard Liengme]

In D81 use =IF(C81=$C$84,"A","")
In D82 use =IF(AND(C81=$C$84, C82=$C$84),"&B",IF( C82=$C$84,"B",""))
In D83 use =IF(AND(OR(C81=$C$84,C82=$C$84), C83=$C$84),"&C",IF(
C83=$C$84,"C",""))
Finally to get the result you aksed for, use =D81&D82&D83

You could just a single formula
=IF(C81=$C$84,"A","")&IF(AND(C81=$C$84, C82=$C$84),"&B",IF(
C82=$C$84,"B",""))&IF(AND(OR(C81=$C$84,C82=$C$84), C83=$C$84),"&C",IF(
C83=$C$84,"C",""))

best wishes

 

[Caren]

Fab fab fab. thank you very much.

In D81 use =IF(C81=$C$84,"A","")
In D82 use =IF(AND(C81=$C$84, C82=$C$84),"&B",IF( C82=$C$84,"B",""))
In D83 use =IF(AND(OR(C81=$C$84,C82=$C$84), C83=$C$84),"&C",IF(
C83=$C$84,"C",""))
Finally to get the result you aksed for, use =D81&D82&D83

You could just a single formula
=IF(C81=$C$84,"A","")&IF(AND(C81=$C$84, C82=$C$84),"&B",IF(
C82=$C$84,"B",""))&IF(AND(OR(C81=$C$84,C82=$C$84), C83=$C$84),"&C",IF(
C83=$C$84,"C",""))

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email