Mode function - return default value rather than #N/A error if no

[Struggling in Sheffield]

Hi,
I'm using the following expression to return the mode of a list of numbers:

=IF(H1028="","",MODE(H1028:H1031))

If the four checked cells all have values but there's no two values the same
(i.e. no mode) what I need is an expression that will return me a default
value of 2 rather than the #N/A error value.

Thanks for looking,
Steve.

 

[Stefi]

=IF(ISERROR(MODE(A1028:A31)),2,MODE(A1028:A31))

Regards,
Stefi

„Struggling in Sheffield†ezt írta:

 

[Struggling in Sheffield]

Hi Stefi,
Thank you very much, worked a treat.
Steve.

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„Struggling in Sheffield†ezt írta:

 

[Domenic]

Here's another way...

=IF(H1028<>"",LOOKUP(9.99999999999999E+307,CHOOSE({1,2},2,MODE(H1028:H103
1))),"")

Hope this helps!

http://www.xl-central.com

Struggling in Sheffield