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