|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 13th, 2009, 11:09 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
MODE calc where more than one mode number!
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. 
|
|
#2
October 14th, 2009, 02:36 AM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
MODE calc where more than one mode number!
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:G950MODE(G945 :G950),G945:G950)))=COUNTIF(G945:G950,MODE(G945:G9 50)),"",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 "Struggling in Sheffield" wrote in message ... 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.
|
|
#3
October 15th, 2009, 04:52 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
MODE calc where more than one mode number!
Cheers Bernie, that works spot on.
Thanks very much, Steve. "Bernie Deitrick" wrote: 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:G950MODE(G945 :G950),G945:G950)))=COUNTIF(G945:G950,MODE(G945:G9 50)),"",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 "Struggling in Sheffield" wrote in message ... 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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
