Anita,

It worked fine for me. If there is only one mode, it returns

TEXT(MODE(A1:A6),"0.0") & " Mode"

which is the mode to one decimal, which may be what leads you to think that the mode is incorrect.
You could change that to just

MODE(A1:A6)

but there wouldn't be anyway to differentiate between mode and average.

HTH,
Bernie
MS Excel MVP


"Anita" wrote in message
...
Hi

Thanks - I've tried it and it works out the average but doesn't calculate
the mode correctly if there is only one mode. What am I doing wrong? I
don't really understand what the formula is doing I just typed it in. But I
want it to calculate the mode if there aren't multiples otherwise average.
Hope I'm making sense.

Many thanks

"Bernie Deitrick" wrote:

Anita,

If your six values are in cells A1:A6, the following array formula - entered using
Ctrl-Shift-Enter - will help you on your way:

=IF(SUM(IF(COUNTIF(A1:A6,A1:A6)=COUNTIF(A1:A6,MODE (A1:A6)),1))/COUNTIF(A1:A6,MODE(A1:A6))=1,TEXT(MODE(A1:A6),"0.0 ")
& " Mode",TEXT(AVERAGE(A1:A6), "0.00") & " Average")

Remove any line-feeds inserted by your browser/mail reader.

HTH,
Bernie
MS Excel MVP


"Anita" wrote in message
...
I have a spreadsheet containing 6 cells say with the following values ...

1 2 3 1 2 3

I want to work out the MODE and if there isn't one, then work out an
average, but the problem is if there is no mode it displays the first cell
value (i.e in the above example that would be 1). Surely this isn't right?

Any suggestions gratefully received.

Thanks

Anita