User Defined Function

How would I write a MinIf function that could be used in the spreadshee
in the same way the "CountIf" function is used.

It would be great to be able to min a range, selecting all values abov
zero.

Any thoughts greatly appreciated

--
Message posted from http://www.ExcelForum.com

0
6/14/2004 8:07:25 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
285 Views

Similar Articles

[PageSpeed] 48

Try something like:

In B1: =MIN(IF(A1:A100>0,A1:A100))

Array-enter with CTRL+SHIFT+ENTER
(instead of just ENTER)
--
Rgds
Max
xl 97
---
Please respond in thread
xdemechanik <at>yahoo<dot>com
----
"dovrox >" <<dovrox.17tzmb@excelforum-nospam.com> wrote in message
news:dovrox.17tzmb@excelforum-nospam.com...
> How would I write a MinIf function that could be used in the spreadsheet
> in the same way the "CountIf" function is used.
>
> It would be great to be able to min a range, selecting all values above
> zero.
>
> Any thoughts greatly appreciated.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
demechanik (4694)
6/14/2004 8:39:32 AM
Hi

You can use standard excel functions for it

With your data p.e. in range A1:A100
=MIN(IF(A1:A100<=0,MAX(A1:A100),A1:A100))
entered as array function (Ctrl+Alt+Enter) will do.


-- 
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)



"dovrox >" <<dovrox.17tzmb@excelforum-nospam.com> wrote in message
news:dovrox.17tzmb@excelforum-nospam.com...
> How would I write a MinIf function that could be used in the spreadsheet
> in the same way the "CountIf" function is used.
>
> It would be great to be able to min a range, selecting all values above
> zero.
>
> Any thoughts greatly appreciated.
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
garbage (651)
6/14/2004 9:02:39 AM
Reply:

Similar Artilces:

Error when adding new user to CRM
Hello all, I need to add 5 new users into CRM. When I complete the first form for the user information such as Last name, First name, email address, etc, then click the save button I receive an error page and the following message is logged in the Application log. MSCRM Platform Error Report: -------------------------------------------------------------------------------------------------------- Error: The statement has been terminated. Error Message: The statement has been terminated. Error Details: Details on this error have not been provided by the platform. Source File: Not avail...

SBS Exchange 2003: Create new user in AD but no exchange mailbox #3
I'm using SBS 2003. Exchange server is SP2. When I create a new user using Active Directory, it prompts me about creating a mail box. It says it's creating a mailbox, then it's done. But no mailbox appears. In fact, if I use the Exchange tasks wizard, I can create, delete, and recreate the mailbox--all say successful without an error message. And yet, no mailbox. This is even after I reboot the PC and sent email to the address. Any ideas what's going on? mike.aes@gmail.com wrote: > I'm using SBS 2003. Exchange server is SP2. > > When I create a new user ...

Lookup Function in user form
I have a combo box (cboitem1) and a text box (txtprice1). I am using the code below to have txtprice1 fill with the corresponding price the worksheet: Private Sub Cboitem1_Change() If cboItem1 <> "" Then txtprice1.Value = Excel.WorksheetFunction.VLookup(cboItem1.Value, Sheets("Pizzas").Range("A1:B65536"), 2, False) End If End Sub It works perfectly when you enter an existing value in cboitem1. However if 'the user' accidently types a letter that is not the first letter of one of the existing items VB debugging pops up with error &qu...

Track in CRM function
Hello. Upon selecting the “Track In CRM” button from the CRM line in Outlook I have two questions based on situation: 1) If the contact exists in CRM it appears to attach the email but provides no notice unless you try to attach it again? True? 2) If the contact does not exist it attaches the email where? In v1.2 it opened a window and made the user create a record. I think it is attaching or tracking the email in CRM somewhere because trying to do it again, I'm asked if you want to attach/track it again. Does this make sense? By the way, we are using CRM 3.0 Thanks. After c...

Excel user information
I am interested in learning about who uses Excel and how they use it. For example, what percentage of users have had formal training; how many use VLookup, or how many build models from scratch. Does anyone know of a source for such information? Thanks. Steve Powell No idea where you would get official stats, if indeed they even exist (Which i doubt), but happy to give you my personal guess:- Formal Training - Less than 1% Use VLOOKUP - Less than 10% Build Models - Depends on definition of what a model is. A couple of cells of data with a formula could be construed as a model, so I...

Tracked changes function misbehaving
I have a simple workbook (2002) in which the Track Changes feature is enabled. End users have reported periodic problems with the contents of cells "mysteriously" changing. In process of investigating these, have found that when tracked changes are highlighted, the comments that describe changes are not always associated with the correct cell. Example, comment on cell B114 states "Changed cell B114 from '<blank>' to '12345'". In fact, contents of cell B114 is "67890" and contents of cell B104 is actually "12345". Has anyone...

Could Microsoft create a MSPOS user manual?
My customers are asking for one. Guess we were spoiled by RMS Store Operations having one available after Microsoft updated the SMS Commerce manuals. Makes me expect one for MSPOS even though most software no longer has printed manuals. -- Jeff Faul Merchants Solutions ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and t...

List of kernel mode compatible functions
Hi, Is there a list of kernel mode compatible C/C++ functions (preferably for WDK 7600)? Thanks, Jan Wouldn't that be the WDK Help file? Thomas F. Divine "Jan M" <jan_m@jerseymail.co.uk> wrote in message news:e7fhXcanKHA.1548@TK2MSFTNGP02.phx.gbl... > Hi, > > Is there a list of kernel mode compatible C/C++ functions (preferably for > WDK 7600)? > > Thanks, > > Jan > Your right of course. I just didnt look hard enough. Jan ...

Set Out of Office (or a rule) to only function on weekends
Hello, I am trying to set up so that every weekend my internal e-mail gets forwarded to my home(smartphone) account. I can only find options to run a rule or out of office assistant to run for specific date ranges. How do I set up either a rule or the out of office assistant to 'push' mail to me only on weekends? HCA.CWT.RN wrote on Fri, 02 April 2010 09:04 > Hello, I am trying to set up so that every weekend my internal e-mail gets > forwarded to my home(smartphone) account. I can only find options to run a > rule or out of office assistant to run for spec...

FORECASTING SALES (please let this be a worksheet function)
Hi I have recently been given the task by my boss to forecast one of ou key customers monthly usage until monthly until end of 2006, I am starting with this customers indidual branches monthly usage fo the past 3 years, Some branches will have opened and closed during thi time, And i am looking to be able to predict monthly usage for the nex 18 months Has anyone got any idea's on the best way to forcast within excel, I a currently using trend (fomulae given to me on this sight) but people ar saying this is not the best way Exponentionally has been mentioned by a few of my collegues but...

Using a OR() like function in an IIF statement
Hello, I am trying to create an IIF statement to test if the first character in a field is a 1,2,8 or 9. Something like the following: IIf(Left([possible_SO_match],1)="1 or 2 or 8 or 9",[Possible_SO_Match],"No Match") Is there a way to create it without going to a 4 level nested IIF statement? Thanks, Kerry -- Message posted via http://www.accessmonster.com kkulakow via AccessMonster.com wrote: > Hello, I am trying to create an IIF statement to test if the first > character in a field is a 1,2,8 or 9. > Something like the following: > > IIf(Left([possib...

User Template crashes Outlook2007
When I select a distribution template from "User Templates in File System" Outlook crashes, sends error amessage, and reloads Outlook. Can't weem to find the problem. ...

Adding User Name to footer
I have spreadsheets that go out to a number of users, and they make various changes and print out for themselves. The problems is that no-one knows which version to pick up from a central printer. Can I add the user name to the footer. I also have the path and file in the same area. I would like to end up with : User Name printed 'path/file' on 'date' ...

How do you define a custom paper size in Excel? (i.e. 11x17)
How do you define a custom paper size in Excel? (i.e. 11x17) Hello- Excel doesn't provide for custom paper sizes, but most any size appropriate for Excel output is already provided _if_ the installed print driver supports it. Go to File>Page SetUp--Page and open the list of sizes. If your installed driver supports 11x17 it will be in the list (although it may be listed as 'Ledger' rather than by dimensions). HTH |:>) "Marcin Rembisz" wrote: > How do you define a custom paper size in Excel? (i.e. 11x17) ...

Transferring users from 1.2 to 3.0 upgrade
I am trying to upgrade a 25-user 1.2 installation to a 10-user 3.0 setup. Only 7 of the 25 licenses are being used. When I run the setup, I receive the following message: There are not enough Microsoft CRM 3.0 licenses to include all the users from Microsoft CRM 1.2 in the upgrade. Please add enough licenses to cover all the active users or deactivate some users to match the number of licenses. As I said there are only seven active users, so there shouldn't be a problem. Does anyone know what I need to do to fix this problem? Thanks. Berk; I've never seen the error, but p...

RMS User Question/Problem
Hello, I Downloaded and installed Microsoft Dynamics RMS, but when I first run it (for the first time) it asks me user ID and password, but have not yet established one, and is the first time I run the program! (Or is it comes with some standard user / default?) User ID: 1 Password: password Robert Armstrong RMS Systems Inc. "F.Misle" <F.Misle@discussions.microsoft.com> wrote in message news:B29A28E5-315C-4A1A-9490-A497651736A0@microsoft.com... > Hello, > I Downloaded and installed Microsoft Dynamics RMS, but when I first run it > (for the first...

user name and password not being retained in WindowsMail
I have Vista 32bit sp2, IE8 and WindowsMail. I had this problem on my XP/Outlook Express machine and found an answer, but can't find one for WindowsMail. Please note that I have had this set-up for several months and never incurred this problem. Even earlier today everything was working fine. It looks as if I can send messages, but when I click on send/receive button to receive messages I get a screen asking for my username and password. I have it set to remember in 'tools' 'accounts', but it isn't remembering it. Does anyone have a suggestion on how...

Need to insert a picture using a function
I would like to know if it is possible to insert a picture (.jpg) using a function. I have a simple quote sheet setup for my customers and would like to insert a picture of the product next to the quote information. Is it possible to insert a picture based on what i enter as my product number?? I know this is a vague question, sorry. Someone help please!!! Try this link for a possible solution:- http://www.mcgimpsey.com/excel/lookuppics.html -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 00/02/03 -------------...

e2k3 sp2: Unknown user name or bad password error
We have an exchange cluster with two nodes that runs exchange 2003 SP2 on windows 2003 Sp1 ent. For security reasons recently I had to change the password for the account under which I installed exchange - the account is exchservice. Since then I get few of the following messages in the security log on the server - any idea how can I stop this? Event Type: Failure Audit Event Source: Security Event Category: Logon/Logoff Event ID: 529 Date: 09/10/2006 Time: 10:30:00 User: NT AUTHORITY\SYSTEM Computer: EMILYA Description: Logon Failure: Reason: Unknown user name or bad password Us...

Is any group function which concatenates data?
Hello. In a query as follows I would like to concatenate data instead of aggregating in a field which is not a part of group clause. SELECT invoice_no, device_type, concatenate_text(device_serial),customer FROM t1,t2,t3,t4 WHERE .... group by invoice_no, device_type, customer Full data set without grouping looks like: SRW/1/2010, AAASSSQQWE, sn000001, ABIX SRW/2/2010, AAASSSQQWE, MSD001XXX, ADA SRW/2/2010, AAASSSQQWE, sn000002, ADA SRW/3/2010, AAASSSQQWE, sn000001, ADA SRW/4/2010, AAASSSQQWE, sn000001, ADT I would like to have something like this after group by and "co...

Disable COPY function
Does anyone know how can I avoid/restrict people Copying-n- pasting data from my excel spread-sheet. I am trying to lock down an excel spread-sheet to "Read Only" - in its true sense and dis-allow even copying data from this sheet to any other. Basically I want to turn off the COPY function. Secondly, if a person saves this sheet locally under another file name, how would I still disallow the COPY function. Is there any VBA module (which I can later password protect) which takes care of this. I do not have admin rights to set folder/file server permissions and the hiararch...

Delivery failures to my users
This might be too broad of a question, but one of my users occasionaly will not get emails from the outside world. I've checked the Event Viewer, modified the Diagnostic Logging Levels, but can't find a reason why she's not getting some emails. Below is what her sender got back when attempting to email her. I obviously changed some information to protect the innocent! Our enviornment is: 1 Exchange 2003 Enterprise Front End Server 1 Exchange 2003 Enterprise Back End Server (ispriv and ispub on here) 1 Exchange 5.5 Enterprise We are in the middle of moving mailboxes frmo 5.5 to 2...

MROUND function disappears
When using MROUND, the MROUND function turns itself off randomly and all the cells that used MROUND become non-functional. To restart MROUND, we have to uncheck the Analysis Toolpak and then checkmark it again. We don't reboot or close the worksheet. This happens on Excel XP and 2003, on all different computers. Present PC is XP Pro, 3GHz P4, 1GB RAM. The excel files are located on a Windows 2000 server. This is becoming very frustrating for the user. We can't find any posts on the subject. I can't beleive we're the only ones experiencing this problem. Any help w...

add public folder calendar to multiple user accounts favorite calendar list. 06-22-10
hey all, i need to add a public folder calendar to multiple user accounts "favorites" calendar list (shows as other calendars) in outlook 2007. i know, from outlook, you can right click and add to favorite to add it to an individual user's "other calendars" but i need to do this for a group of people. is there any way in exchange, gpo, or something to do this automatically based on group membership, etc.? thanks in advance. shrpshtr ...

Loss of Functionality questions
If I understand correctly, sometime in the next month or two I will lose the ability to update stock quotes in MS Money 2003. Will I still be able to download from my banks and brokerages and CheckFree? As far as I know, these connect directly and not via the MSN website. Is there any way, other than manually entering each price, to get quote data into MS Money once the automatic download facility expires? Thanks. It does not seem to me as if the later versions of Money have any features that I would use or have fixed the bugs and annoyances that have appeared over the past years, so I am ...