Excel formula help

  QuizMan 16:00 12 Jun 09
Locked

I am devising a spreadsheet to evaluate training course feedback. One of the questions is about the pace of the course and the possible responses are "too fast", "too slow" or "just right" in the range C18:N18. I need a formula to return the most frequent response within this range. I have tried MODE, but that seems to work with numeric input only. Can anyone point me in the right direction please?

  OTT_Buzzard 16:48 12 Jun 09

I can't think of a way to do this in a single formula without making it over complicated.

You can use

=countif(C18:n18,"too fast")
=countif(C18:n18,"too slow")
=countif(C18:n18,"just right")

in 3 separate cells, then use =max(range) to determine the highest result from the above 3 formulae.

The only other alternative i can think of is to nest a whole bunch of "countif" and "if" statements together to test each option together to determine the final results - it would however be a very long formula!!

  OTT_Buzzard 16:48 12 Jun 09

or a custom function may be easier if the above solution doesn't suit the layout of your workbook.

  Picklefactory 17:05 12 Jun 09

What would you like to happen if there is a tie? Is your syntax guaranteed to be correct on the responses?

  QuizMan 17:06 12 Jun 09

I am grateful for your help. Thank you. I have got the various countif formulae to work, but the MAX formula only seems to return numeric data. I am now trying to think of a way of converting that back to text. I shall play around with it for a while to see what I can come up with, but will not tick for now.

  VoG II 17:19 12 Jun 09

Try

=INDEX(C18:N18,MATCH(MAX(COUNTIF(C18:N18,C18:N18&"")),COUNTIF(C18:N18,C18:N18&""),0))

which is an array formula that must be entered by holding down CTRL and Shift then pressing Enter. If entered correctly Excel will surround the formula in the formula bar with curly braces {} - don't try entering these yourself.

  QuizMan 18:00 12 Jun 09

Many thanks. I have managed to input the formula as an array, but it returns 0 (zero) every time. You have given me an idea that I shall try using the LARGE formula in association with COUNTIF, but I have to go out in a few minutes so will have to leave it for later.

I am very grateful to all for the advice.

  QuizMan 18:07 12 Jun 09

Correction - it does not seem to works if the number of blank cells exceeds the cells with the highest occurence of data input. There is a possibility that less than half the cells will have data in them. Is there an option to ignore blanks perhaps?

  VoG II 19:03 12 Jun 09

I'm sorry but I can't find a way to eliminate blanks using a formula (formulas are not my strong point). Perhaps a User Defined Function...

Press ALT + F11 to open the Visual Basic Editor, Insert > Module and paste into the white space on the right:



Function Maxtext(r As Range) As String
Dim Cats, c As Range, K(0 To 2), i As Integer
Application.Volatile
Cats = Array("too fast", "too slow", "just right")
For Each c In r
For i = 0 To 2
If c.Value = Cats(i) Then
K(i) = K(i) + 1
Exit For
End If
Next i
Next c
For i = 0 To 2
If K(i) = WorksheetFunction.Max(K) Then
Maxtext = Cats(i)
Exit For
End If
Next i
End Function


Then press ALT + Q to return to your sheet. Then enter the formula

=maxtext(C18:N18)

and press Enter (no need for CTRl + Shift + Enter).

  QuizMan 08:46 13 Jun 09

Wow, what can I say? Thank you very much for that and your clear instructions. It worked a treat. I really appreciate it.

This thread is now locked and can not be replied to.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

The Pantone Colour of the Year 2017 is Green

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…