MODE calc where more than one mode number!

  • Thread starter Struggling in Sheffield
  • Start date
S

Struggling in Sheffield

Hi all,
When I carry out a MODE calc on a range of numbers, if there is more than
one mode, the calc is returning the first mode encountered:

0 0 4 2 2 1

In the range of six numbers above there is no clear mode as both '0' and '2'
occur twice. My calc returns '0' as the mode (presumably because this is the
first occuring mode that it encounters?). However this is no good to me,
where there is no clear mode I need the calc to return a blank cell (not an
Iserror).

Present formula:

=MODE(OFFSET(G950,,,-6,))

Can't find any help on this at all, if someone knows a way around my problem
it'd be much appreciated.

Cheers,
Steve.
 
B

Bernie Deitrick

You can do it by extracting the second mode, and counting / comparing the
instances of the first and second modes...

Array enter (enter using Ctrl-Shift-Enter) the formula

=IF(COUNTIF(G945:G950,MODE(IF(G945:G950<>MODE(G945:G950),G945:G950)))=COUNTIF(G945:G950,MODE(G945:G950)),"",MODE(G945:G950))

Or, in keeping with your offset style:

=IF(COUNTIF(OFFSET(G950,,,-6,),MODE(IF(OFFSET(G950,,,-6,)<>MODE(OFFSET(G950,,,-6,)),OFFSET(G950,,,-6,))))=COUNTIF(OFFSET(G950,,,-6,),MODE(OFFSET(G950,,,-6,))),"",MODE(OFFSET(G950,,,-6,)))


HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top