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
605 Views

Similar Articles

[PageSpeed] 29

=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:

Which Function...
will allow me to calculate a value on a form depending on the information in two other fields? For instance: If ad_Advertiser can have 3 values Private party, Commercial or National and each has a different rate and the cost of an ad is dependent on the number of words in the ad, i would expect to write something like this: CCUR(IIF([ad_Advertiser]=Private party, 3.50+(([wordcount]-10)*.35); IIF([ad_Advertiser]=Commercial, 4.50+(([wordcount]-10)*.45); IIF([ad_Advertiser]=National, 6.50+(([wordcount]-10)*.65) I know that the IIF function is not correct but not sure what to use in its place...

Data errors on HP Mydrive NAS
I have a strange issue with a HP Mydrive "World edition" connected to ethernet. Files copied to this drive sometimes get corrupted, in a very specific pattern. We cannot reproduce this corruption with smaller files, less than 2 MB, but with files > 20 MB it occurs in 80% of copies. I copy the files by drag & drop from Explorer, or by copy / v command, results are same. More over, copy /v does not find any error. Then I run fc /b <copy> <original> and it finds differences. In a ~ 20 MB file, there are few bytes (1 to 6) with one bit (same in all ...

MS Store Operations v1.2 error creating new customer
When I hit F7 "New" I get the error "you are not allowed to create new customers at the POS" Anyone know why this is happening? It used to work. Thanks! HI Adrain, Login to Store Manager and then Go to File > Configuration> click on Options tab and then click on "Customer Options" then select "Allow new customer at POS" This by the the way resolve your issue but you have other side also to check. go to Database > Cashier and click on your cashier information and below the security block check that "Allow to view and edit all custo...

How to know default email in outlook from a c# program
Hi, I need to access the default email selected in outlook through a C# application. Thanks, Olivier Olivier Verdin wrote: > I need to access the default email selected in outlook through a C# > application. You might have a better luck asking this question in one of the dotnet groups. [Listening to: Michael Jackson - Off the Wall] -- Alex Yu http://mucha.us/blogs/alex/ This posting represents my own opinion, and in no way claims to represent the views of Microsoft Corporation. This posting is provided "as is" with no warranties, and confers no rights. Please do no...

Error 2349
Note please contact support for assistance. Having problems with operating Home Publisher 2000. Help Home Publishing has its own newsgroup news://msnews.microsoft.com/microsoft.public.home.publishing This error will appear when the CD is dirty or damaged. -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/msauer/ news://msnews.microsoft.com "Erk" <anonymous@discussions.microsoft.com> wrote in message news:ac5f01c48825$936231b0$a601280a@phx.gbl... > Note please contact support for assistance. Having > problems with operating Home Publisher 2000...

Dll function not inserting record
i am using this code for inserting the data...and iam using oledb consumer wizard to make connection....can any body point out what is the problem here...it always get user define run time error..."Error inserting the record"...after calling dll function...any help typedef int (CALLBACK* Set_Quote_DB)(unsigned short**,short*,unsigned short**,unsigned short**,unsigned short**,short*); void CArielProjectVer2Dlg::OnPriceChangeArielapictrl1(LPCTSTR SessionId, LPCTSTR RequestId, short MarketNo, LPCTSTR Market, LPCTSTR Bid, short BidDirection, LPCTSTR Ask, short AskDirection, LPCTSTR H...

Macro
I have a Macro which finds a unique value in a sheet, but I then want it to move down 6 rows from where it finds that value to do something else. What is the code to 'move' around by x number of cells ? -- http://www.redbrick.dcu.ie/~pele You can use .offset() from the cell you found: Option Explicit Sub testme01() Dim FoundCell As Range Dim FindWhat As String FindWhat = "asdf" With ActiveSheet.UsedRange Set FoundCell = .Cells.Find(What:=FindWhat, _ After:=.Cells(.Cells.Count), _ ...

Working in Cache-Mode in Outlook 2003
I notice that there is a significant delay when I have any of my users set for Cached-Mode compared to connecting directly to Exchange. Is there anyway to speed this up? Well this is the benefit of Cached Exchange mode; by not checking mail constantly you reduce network bandwidth being used. Cached Exchange mode downloads messages 30 seconds after the Exchange server notices the Outlook client of changes. Then all changes within those 30 seconds will be synchronized at once. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean...

Error while starting outlook
Hi I am using Outlook XP 2003. When ever i start outlook it gives me this error message CiceroUNIwndFrame outlook.exe error ~~~~~~~~~~~~~~~~~~~~~~~ the procedure entry point GetIUMS could not be located in MSDART.DLL I have tried to use the detect & repair function but still did not resolve the issue. any suggestions Regards Sam -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Privileged/Confidential Information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such ...

Defauls Values In Empty Cells
How do I set a default value which has no data imported into it? Explain a little more about your needs, please. By definition a cell with no data imported into it has no value. It is Empty. You cannot control Excel's default cell contents, as when a new sheet is entered. There are things that can be done via macros, and some things that can be done with worksheet functions, such as putting this formula in a cell (it could go into any cell except B1 itself: =IF(B1=9,"B1 is 9","B1 is not 9") you can substitute other phrases for what is in that example, or even ...

How do I get docs to display in Details mode instead of idons?
When I open a folder in MS Word, in order to see the documents, I see a bunch of icons. I want to see a Details view, which shows the name and date of each document. And I don't need to see how much space it occupies. How do I set it up to do this, instead of changing the view each time? hi Leslie, You could try setting up the view as you want it & then pressing [ctrl + cancel]. I'm not sure if it will work for you, but if not, have a read of some of the suggestions listed in this search: 'settings in file open dialogbox to show details - Google Search' (h...

Rate Type Error Message
In Financial Batch Edit List this error message came out: "The rate type ID is not associated with the currency ID for this transaction" ...

Name Function
I have a form (frm_DeleteJob) when the user opens this form the following fields are displayed; Operator Name (i.e. person to created entry), Quantity, Date and Comment. Now I also what to put the name of the person who's actually going to delete the record, but when I go back into the query thats running this form I can't place the Operator field twice because then it would just give me the same info. Any ideas as to how I can do this? Thanks Asif CurrentUser() returns the name of the person logged into access You can use this to display the name on the form and to record a delet...

Outlook 2007
I'm trying to switch over to OL2007 from Thunderbird. I have several email accounts, some IMAP, some POP3. It really gripes me the way OL2007 handles multiple accounts. Sending an email from any of my accounts defaults to having been sent from a default account. What's so hard about composing an email from within a particular "Inbox" and having the email appear to have been sent from that account?? Instead, unless I remember to change it manually each and every time, OL2007 will cause the email will appear to have been sent from another address entirely. For example I w...

Report Format changes after leaving design mode
I've just about finished setting up a new report, except for one field. It is meant to be located between two thick vertical lines, and under another label/text set. No matter what I do, the preview version puts this data and label (a date) at the bottom of that record's section, near the bottom line of data. I have seavel text boxes set up to to display data from seavel different fields below one another, without leaving gaps, one of which is large enough to change the vertical size of the record. the problem seems to be related to this text, but if that is true or how it is related, ...

Converting Values
I have cell C10 with a currency value. It also has "strikethrough" formatting. I want to convert the value in C10 to "$0.00" where the cell has "strikethrough" formatting. Any suggestions are appreciated. Thanks The following code will change all applicable cells in the selected range: '============================== Sub ChangeStrikeToZero() Dim c As Range For Each c In Selection If c.Font.Strikethrough = True Then c.Value = 0 End If Next c End Sub '============================== Eric wrote: > I have cell C10 with a currency value....

Task Scheduler error
A long time ago I scheduled a task to load up a certain utility at 7 am. However, I've just realized it never gets loaded. I went into Task Scheduler to see what's going on, and as it opened I get this error: "An error has occured for task Reminders - owner. Error message: the specified account name is not valid" I'm looking at my task and I see that "When running a task, use the following user account" is set to "RICK \owner". I'm not sure how to check if that's a valid account name. I don't log on to my computer wi...

Creating a Pivot Table via Macro
I have an Excel workbook that is getting generated from another tool (Quality Center). It has output data on a tab called "FailedBlocked" and another tab that is empty called "TC by Def". I have recorded a macro to create the pivot table. The code Excel creates is: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "FailedBlocked!R1C1:R1090C13", Version:=xlPivotTableVersion10). _ CreatePivotTable TableDestination:="TC by Def!R1C1", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTa...

STANDBY mode
Hi how to put the database to standby mode for log shipping: *** Error: The restore operation cannot proceed because the secondary database 'mydatabase' is not in NORECOVERY/STANDBY mode.(Microsoft.SqlServer.Management.LogShipping) Thanks... You can't. I.e., you need to re-do a prior database restore and get going from there. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi <someone@js.com> wrote in message news:#ww1cVQpKHA.5224@TK2MSFTNGP05.phx.gbl... > Hi how to put the dat...

Label printing defaults
Can I change the default on the lables so that it automatically sets it to print only 1 lablel instead of a whole sheet of labels. If so, how would it be done? Thanks! You could, but you will still have to indicate which single label. It would be rather difficult (and impractical) to have the dialog choose the next available label. You would would need to store the label format, and thus the number of labels and increment a counter each time you use the facility. I foresee far too many problems to make this worth the effort of development. For Word 2003 use the following m...

Is there a SPELL function or its equivalent?
I am trying to detect, not correct, spelling errors in a list of words and non-words. I would like a function that will take a string in a cell specified in one of the usual ways that would give me a logical value of TRUE if it is spelled correctly and FALSE if it spelled incorrectly. I could then sort on those values to segregate them into words and non-words. At this time. I am willing to use the default dictionary or a specified dictionary for the purpose. A macro would be OK as well. If I understand the way spell checking works, each error has to be handled individually for correct...

Operation cannot be performed error in Money 2004
I am using Money 2004 Deluxe and when I start money at the passport screen after clicking next, I get a message that says that the operation cannot be performed. I tried to repair the file but this did not work. Does anyone have any ideas what this problem might be?? Thank You See http://www.bollar.org/msmoney/#Q104. "Matthew" <mhodges1338@optonline.net> wrote in message news:Pfbwb.20195$Hb.7211828@news4.srv.hcvlny.cv.net... > I am using Money 2004 Deluxe and when I start money at the passport screen > after clicking next, I get a message that says that the operation...

LOOKUP function does not work with row vector of numbers
Here is a spread sheet on Excel 2003: A B 1 =1.11+2 2 2 =LOOKUP(3.11,A1:B1) The B2 cell is #N/A, although A1 is evaluated to be 3.11 It seems if you match against 1.1+2, it works. Could anyone tell me where things is going wrong? Thanks What are you trying to do? Do you want to return the *last* (rightmost) numeric value in the range? If so, try this: =LOOKUP(1E100,A1:B1) -- Biff Microsoft Excel MVP "JRaSH" <jrash06@163.com> wrote in message news:O50aaD2iKHA.1824@TK2MSFTNGP04.phx.gbl... > Here is a...

Example attachment for drop down menus and functions post
An example of what I'm trying to do, probably would help generate some better responses. Attachment filename: example.xls Download attachment: http://www.excelforum.com/attachment.php?postid=646989 --- Message posted from http://www.ExcelForum.com/ One way .. Put in E6: =IF(OR(COUNTIF(C6:D6,"A")<>1,C6="",D6=""),"Recheck entries in col C and/or col D",OFFSET($H$5,MATCH(IF(D6<>"A",D6,C6),$G$6:$G$10,0),)) Copy E6 down to E9 The above assumes that point "A" (the starting point) will a...

Same Publisher 2002 error
I'm still getting the smae error when starting Publisher 2002. I had no problems with install this time. Publisher won't progress past loading without this error: "The instruction at "0x77f580de" referenced memory at "0x0000000". The memory could not be "written". I am still unable to find this error message at Microsoft. Any suggestions? Read the reply to your previous message!!! -- I did that. I have read my previous post and that didn't resolve the issue! ...