Excel formula help

  QuizMan 16:00 12 Jun 2009

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 2009

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 2009

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

  Picklefactory 17:05 12 Jun 2009

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 2009

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 2009



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 2009

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 2009

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 2009

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
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


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

  QuizMan 08:46 13 Jun 2009

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.

Elsewhere on IDG sites

Best phone camera 2017

Stunning new film posters by Hattie Stewart, Joe Cruz & more

iPad Pro 10.5in (2017) review

28 astuces pour profiter au mieux de votre iPhone