Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
potential duplicate max values
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
potential duplicate max values
Please so not multipost; crossposting is OK
See answer in other group best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Caren" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
potential duplicate max values
Thank you very much (just a quick reason for the multiple post - the system
rejected the first one, so I retyped, worried it was going to lose my data again, I copied the content ! The system again said query failed, so I posted for a 3rd time, and then all 3 arrived.) Apologies. Thank you very very much for the reply - worked a dream. "Bernard Liengme" wrote: Please so not multipost; crossposting is OK See answer in other group best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Caren" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Potential Bug in Find All application | Excel Discussion (Misc queries) | |||
DUPLICATE VALUES | Excel Discussion (Misc queries) | |||
Potential CSV/Excel Conversion Issues? | Excel Discussion (Misc queries) | |||
Formula How to add potential nos | Excel Worksheet Functions | |||
Sum of Duplicate Values | Excel Worksheet Functions |