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

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.
0
3/26/2009 1:35:01 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
756 Views

Similar Articles

[PageSpeed] 23

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

Regards,
Stefi

„Struggling in Sheffield” ezt írta:

> 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.
0
Stefi (275)
3/26/2009 1:45:02 PM
Hi Stefi,
Thank you very much, worked a treat.
Steve.

"Stefi" wrote:

> =IF(ISERROR(MODE(A1028:A31)),2,MODE(A1028:A31))
> 
> Regards,
> Stefi
> 
> „Struggling in Sheffield” ezt írta:
> 
> > 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.
0
3/27/2009 1:02:02 AM
You are welcome! Thanks for the feedback!
Stefi

„Struggling in Sheffield” ezt írta:

> Hi Stefi,
> Thank you very much, worked a treat.
> Steve.
> 
> "Stefi" wrote:
> 
> > =IF(ISERROR(MODE(A1028:A31)),2,MODE(A1028:A31))
> > 
> > Regards,
> > Stefi
> > 
> > „Struggling in Sheffield” ezt írta:
> > 
> > > 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.
0
Stefi (275)
3/27/2009 7:31:03 AM
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

In article <4329EBA6-F646-4720-9BE3-793BE23887AF@microsoft.com>,
 Struggling in Sheffield 
 <StrugglinginSheffield@discussions.microsoft.com> wrote:

> 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.
0
someone528 (62)
3/27/2009 6:27:49 PM
Reply:

Similar Artilces:

Compile error: label not defined
Hi everybody, I have two forms; I'm trying to requery a couple of fields on the first form when I close out of the second form. I have the following code on the OnClose event of the second form: Private Sub Form_Close() On Error GoTo Err_Handler Forms!frmReferralTracking!RefDoctorID.Requery Forms!frmReferralTracking!refDoctorLast.Requery End Sub Err_Handler_Exit: Exit Sub Err_Handler: MsgBox Err.Number & " - " & Err.Description & " - Form_Close()" Resume Err_Handler_Exit End Sub This is giving me a "Compile error: Label n...

Trying to change txt color from message map function
HI, My menu has a option that changes the text color. so I have my OnPaint() function but I am trying to call the DC from another function in same class.. what am I doing wrong? void CChildView::OnTextBlack() { // TODO: Add your command handler code here CDC* pDC = GetDC(); pDC->SetTextColor(RGB(0,0,0)); pDC->TextOut(mPt.x, mPt.y, mStr); ReleaseDC(pDC); mCount = 1; Invalidate(); } I changed my approach to ON_COMMAND_RANGE... and came up with this... it worked... but why not the first code? void CChildView::OnTextColor(UINT nID) { CClientDC dc(this); switch(nID) { ca...

persistent error for altered or nonexistent line
When I am getting a persistent error (in an imported C header), even when the particular line has been commented out, edited or even removed, where is the error coming from, and how can I flush it? c:\hand in hand\test\vorbisfile.h(27) : fatal error C1083: Cannot open include file: 'codec.h': No such file or directory "Steve Russell" <srussell@removethisinnernet.net> wrote in message news:OKDr%23BFdFHA.2180@TK2MSFTNGP12.phx.gbl... > When I am getting a persistent error (in an imported C header), even when > the particular line has been commented out, edi...

Counting spefic cell (not a range) with a value greater than 1
I am looking for a formula to count specific cells and a range (A5, A10, A15, A20 and so on) but only count when the value is greater than $1.00. So if the value of cell A5 was $1.50, the value of cell A10 was $1.25, the value of cell A15 was $0.50 and the value of cell A20 was $2.00 the total count would be 3. I'm stumped! =SUMPRODUCT(--(LARGE((A5,A10,A15,A20,A25),ROW(INDIRECT("1:"&COUNT(A5,A10,A15,A20,A25))))>1)) however if you want to sum every 5th cell greater than 1 you can use =SUMPRODUCT(--(MOD(ROW(A5:A50),5)=0),--(A5:A50>1)) -- Regards, Peo Sjoblo...

cannot open excel file
I go to open an excel spreadsheet that was working perfectly fine on Friday (it is now Monday), and I get the following error message (regardless of Macro settings): "EXCEL.exe has generated errors and will be closed by Windows. You will need to restart the program. An error log is being created." I did a search of all "*.log" files on my computer, and the only one modified at the same time as the error is drwtsn32.log. Here's the entry I get from the log file: "Application exception occurred: App: (pid=624) When: 6/6/2005 @ 10:28:51.290 ...

ActiveX control and design mode
Hi, is it possible for an activex control to tell the container to invoke the controls design mode? I'm developing an activex control to be used in office, and I would like the control to start in design mode when inserted into powerpoint for instance. Regards Johan ...

copy n past
I am going to have some poems published, how can I send them to the publisher, he cannot open them as they have been writen using 2003, I have tried copy and paste into notpad, but it does not work, they cannot open my publication. katiemouse <g''day@australia.com> wrote: >I am going to have some poems published, how can I send them to the >publisher, he cannot open them as they have been writen using 2003, I have >tried copy and paste into notpad, but it does not work, they cannot open my >publication. Convert to PDF In addition to Uncle's fine advice. ...

Errors after removing old Exchange server
After removing our old Exchange 2000 server we have issues with Outlook 2003 clients getting error "0x8004010f" when clicking the send receive button. After doing a bit of research I beleive that the old server still housed the following: OAB Version 2 Offline Address Book Schedule+ Free Busy Information I cannot bring the old server back to life now ! Is there a way to rehome these functions to the new Exchange 2003 server manually??? If we did not replicate the system folders off of the decommissioned server we will need to recreate them. You can use a utility that is avail...

Set desktop for default save in windows 7
How do I et desktop for default save in windows 7 In article <11B579ED-5C7D-46D5-943F-CCDE6C961D95@microsoft.com>, Mc wrote: > How do I et desktop for default save in windows 7 To be clear on what you're after: You want to set things so your office programs save by default to the Window desktop, is that correct? What version of Office do you have? ...

how do i use outlook as my default?
i do not know how to make outlook work. I have it installed but, when I try to send an e-mail it always gives me a message that says error cannot find server or something of that sort...I use yahoo..but cannot use outlook..can anyone help? nina <nina@discussions.microsoft.com> wrote: > i do not know how to make outlook work. I have it installed but, when > I try to send an e-mail it always gives me a message that says error > cannot find server or something of that sort...I use yahoo..but > cannot use outlook..can anyone help? If you're using yahoo.com in the server...

help! dimension members how to not be selected by default
for a dimension in pivot table,imagining only several of its members are what I am interested to concern;if some new members are added into this dimension when refreash the pivot table,I find these new members are selected automatically, this is an annoyance since I just want to see specific members not new ones. Is this an Excel config option to pre-select new members? Can this option be turned off? thanks ...

message interface returns unknown error
Hello. I am using Outlook 2003 with an exchange server (SBS2003) on an XP SP2 box. Suddenly today I'm finding I can't delete or move some mail items from my Inbox. Not all, just some. I get the message "message interface returns unknown error". What would cause this to start happening and how do I get it to stop? I checked the MS Knowledge base. I saw a couple of references to the message but the situations were different. Any thoughts?? Thanks in advance... Which ones did you try already? Start with the eassiest ones; -empty the Deleted Items folder -rebuil...

Changing PORT of Default CRM Site
I recently installed 3.0 and installed it in the Default web site, which is obviously Port 80. I did some port forwarding in my firewall to make it acccessible from the outside of our network and need to change the Port. I changed it in IIS but evidently there is another location that also needs changing, as the CRM errors when trying to load data. Any ideas or suggestions? Thanks Hi Stan, In the registry, HKLM\software\microsoft\mscrm "Stan" <Stan@discussions.microsoft.com> wrote in message news:BD5A13DB-58AE-4A11-82A4-DEA25B89C966@microsoft.com... >I recent...

The computer restarted unexpectedly or has encountered an unexpected error.
Hello people, After I haven�t used my PC(vista) for two weeks I started it and anything looked normal but when the vista green background appears(where you normally choose the user) suddenly this error pops up: �The computer restarted unexpectedly or has encountered an unexpected error. Windows installation cannot proceed. To install Windows, click OK to restart your computer and then restart installation.� The only thing I can do is press Ok, if I do so my computer restarts and the same error appears. Some things I can and can�t do: I can�t reach my desktop. I can�t choo...

Error on on Outlook Client during tracking Contact in CRM
Hi everyone, I'm in a trouble about CRM 4.0 Outlook Client. The problem is: When the user selects and tries to track a contact in CRM, it gives an error message like "An error occured during tracking operation" (I could not copy the whole message but I can assure you nothing informative was given.) Despite it gives an error when I checked the CRM I saw that the contact record is created on the CRM. When I turn back and double check the CRM flags in the Outlook (from the developer ribbon --> design this form view) I noticed no flags had set, so after the Outlook synchro...

how do I make Excel default to General number format?
I have installed Excel 97 but the General cell format insists on formatting as the number format with two decimal places. Is the number you enter already a two decimal number? General removes any type of formating, and represents the contens as WhatYouSeeIsWhatYouGet "rjmo" wrote: > I have installed Excel 97 but the General cell format insists on formatting > as the number format with two decimal places. You may have accidentally changed the "Normal" Style. Go to Format | Style... and make sure "General" is applied to the "Normal" style. ...

VLOOKUP to return Cell Address
I have a table, and I want to do a Vlookup on Column1, and when I find what I am looking for, I want to sum column 2 from that row down to the bottom of the table. For example, Item 1 2345 Item 2 3478 Item 3 1298 Item 4 123 Item 5 1233 I want to find "Item 3" and then I want to be able to return the sum of (1298+123+1233). How would I do this? My thoughts were that I would do a lookup to get the row numner of "Item 3" and then do a sum() with the cell range starting from the row where I found "Item 3" but I'm not sur...

Error 2950
I have a form that on open runs a query. On close it runs a second query. Both are dealing with a file that is keeping track of how much time I spend on the form. But, when the form is open, you cannot run the query to look at the time. I get a 2950 error telling me that the query is too complex. If I close the form, then the query can run. I tried to close the table after the first query runs, but that did not seem to help. Any ideas? ...

Exchange rule deploy wizard unknown error
Hi, we have an installation of CRM 3.0 with language italian, and we have installed the EMail Router component on the exchange server. When we try to use the deploy wizard in order to create rule for CRM on exchange, all the users of our domain seems to be incorrect, but no additional description is given. The wizard show me an "Unknown Error" (!!??@##) 0x80005000. Nothing in the event viewer. Some one know what's the problem ? Thanks in advance. MAX ...

odd scroll bar behaviour in access, keeps returning to top
hi i have a user here, using ms office 2003, and he has noticed that when scrolling through ms access or excel, that often the vertical scroll bar he is using will return to the top position. this is the same for excel. its a bit of an issue, as he would be clicking through databases with thousands of rows of data, and it keeps retuning to the top of the row. i did notice that a program called ifinger kept popping up as this was happening. but i could not find info on the web linking this product with the issue he is having. if any one has any suggestions, id appreaciate it. Regards ...

outlook 2000 mailbox.pst error. inbox repair tool
hi all I am getting a error message from outlook 2000 that says errors have been detected in the file c:\windows\application data\microsoft\outlook\mailbox.pst It says to use the inbox repair tool. I have searced microsoft online help and found the site "ol2000 how to find and run the inbox repair tool" I am using ME. I have been looking for "Scanpst.exe" file. Microsoft help says to follow c:\program files\common\system\Mapi\1033\nt I cannnot find a file by this name. I can find a file c:\program files\common\system\Mapi\1033\95 when I run "Scanpst.exe&qu...

SP3 Install Error #2
I have a problem installing V10 SP3 which has the following error when upgrading a company: ERROR [Microsoft][SQL Native Client][SQL Server]An explicit value for the identity column in table 'SVC00950' can only be specified when a column list is used and IDENTITY_INSERT is ON. I have tried it with and without the Native driver. How did our system get out of sync with the updates? GP is running on Srv2008 64bit and SQL2008, same machine in a test environment. Any help is much appreciated. Steve Steve, First, Check if you have Field Service registered. If not, You can run...

How do extract cells from hundreds of excel files and put in one n
I need to extract expense totals from employee expense reports and put in to one file for import then in to the payroll system fo reimbursement. Lots of unanswered, but needed information with this question. Generally this can be done relatively easily, with a big "IF" - and that IF is whether all of the files you'd be examining (the employee expense reports) have the same format, content and layout. Get in touch with me via email through (remove spaces) Help From @ jlatham site.com and we can gather some more information and probably have a solution in your han...

Unknown Error 0x80040119 #3
Hello! Having some pretty huge issues with this error - Unknown Error 0x80040119 this occurrs when the Send/Receive button is pressed in Outlook 2003 on a Terminal Server session. Scenario: Users accesses email from a Notebooks out of office using VPN. Outlook is configured to connect an exchange server in cached mode. This particular user travels frequently and also accesses their email via Outlook on our Terminal Servers which is when the problem ocurrs with "Unknown Error 0x80040119". KB http://support.microsoft.com/?kbid=814441 not resolve this issue Hello, you should v...

Annoying & unrecognised value
Hoping someone may recognise this. I regularly import data in to excel for use with various look up / count / if functions. Quite often I find that where I have pasted in values that they are not recognised until I go to the specific cell and hard return the value. I have tried cell format; general, number, text ect but makes no difference. I normally use paste special, values only so as not import any format or formula. Can be a real pain on large data sets........... mouse point, enter, next cell, mouse point, enter, next cell, ...... Ay ideas ? D:-) -- Digory ------------------------...