Min value excluding 0 and another criteria

Hi

I am trying to exclude zero plus add another criteria in calculating
the min value of a column.  I want the other criteria to be the text
value of a different column.  For instance column A has dog, cat,
fish, etc (up to 16 category types) and column B has age.  I am trying
to find min age for each category and not include zero.

I know this will find the min age of everything
=MIN(IF($G$5:$G$40>0, $G$5:$G$40))

but i am at a lost for how to incoporate the other criteria

any suggestions would greatly be appreciated
0
4/24/2009 12:41:00 PM
excel 39879 articles. 2 followers. Follow

5 Replies
1051 Views

Similar Articles

[PageSpeed] 26

On Apr 24, 10:41=A0pm, calebmich...@gmail.com wrote:
> Hi
>
> I am trying to exclude zero plus add another criteria in calculating
> the min value of a column. =A0I want the other criteria to be the text
> value of a different column. =A0For instance column A has dog, cat,
> fish, etc (up to 16 category types) and column B has age. =A0I am trying
> to find min age for each category and not include zero.
>
> I know this will find the min age of everything
> =3DMIN(IF($G$5:$G$40>0, $G$5:$G$40))
>
> but i am at a lost for how to incoporate the other criteria
>
> any suggestions would greatly be appreciated

Maybe...

=3DMIN(IF(($G$5:$G$40>0)*($A$5:$A$40=3D"dog"), $G$5:$G$40))
for minimum of dog ages

entered as an array formula.

Ken Johnson
0
KenCJohnson (314)
4/24/2009 2:13:47 PM
On Apr 24, 10:13=A0am, Ken Johnson <KenCJohn...@gmail.com> wrote:
> On Apr 24, 10:41=A0pm, calebmich...@gmail.com wrote:
>
> > Hi
>
> > I am trying to exclude zero plus add another criteria in calculating
> > the min value of a column. =A0I want the other criteria to be the text
> > value of a different column. =A0For instance column A has dog, cat,
> > fish, etc (up to 16 category types) and column B has age. =A0I am tryin=
g
> > to find min age for each category and not include zero.
>
> > I know this will find the min age of everything
> > =3DMIN(IF($G$5:$G$40>0, $G$5:$G$40))
>
> > but i am at a lost for how to incoporate the other criteria
>
> > any suggestions would greatly be appreciated
>
thats returning a zero.
here is what i am using...exchanging dog for a cell regerence

=3DMIN(IF(($G$5:$G$40>0)*($E$5:$E$40=3DE5), $G$5:$G$40))



> Maybe...
>
> =3DMIN(IF(($G$5:$G$40>0)*($A$5:$A$40=3D"dog"), $G$5:$G$40))
> for minimum of dog ages
>
> entered as an array formula.
>
> Ken Johnson

0
4/24/2009 3:16:00 PM
ken

disregard my last posting.  didnt see the line that says to enter as
an array.  works great now....thanks!!!!


On Apr 24, 11:16=A0am, calebmich...@gmail.com wrote:
> On Apr 24, 10:13=A0am, Ken Johnson <KenCJohn...@gmail.com> wrote:
>
>
>
> > On Apr 24, 10:41=A0pm, calebmich...@gmail.com wrote:
>
> > > Hi
>
> > > I am trying to exclude zero plus add another criteria in calculating
> > > the min value of a column. =A0I want the other criteria to be the tex=
t
> > > value of a different column. =A0For instance column A has dog, cat,
> > > fish, etc (up to 16 category types) and column B has age. =A0I am try=
ing
> > > to find min age for each category and not include zero.
>
> > > I know this will find the min age of everything
> > > =3DMIN(IF($G$5:$G$40>0, $G$5:$G$40))
>
> > > but i am at a lost for how to incoporate the other criteria
>
> > > any suggestions would greatly be appreciated
>
> thats returning a zero.
> here is what i am using...exchanging dog for a cell regerence
>
> =3DMIN(IF(($G$5:$G$40>0)*($E$5:$E$40=3DE5), $G$5:$G$40))
>
>
>
> > Maybe...
>
> > =3DMIN(IF(($G$5:$G$40>0)*($A$5:$A$40=3D"dog"), $G$5:$G$40))
> > for minimum of dog ages
>
> > entered as an array formula.
>
> > Ken Johnson- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

0
4/24/2009 3:17:23 PM
disregard last posting.  didnt see the enter as array line.  works
great.  thanks a bunch!!!




On Apr 24, 11:16=A0am, calebmich...@gmail.com wrote:
> On Apr 24, 10:13=A0am, Ken Johnson <KenCJohn...@gmail.com> wrote:
>
>
>
> > On Apr 24, 10:41=A0pm, calebmich...@gmail.com wrote:
>
> > > Hi
>
> > > I am trying to exclude zero plus add another criteria in calculating
> > > the min value of a column. =A0I want the other criteria to be the tex=
t
> > > value of a different column. =A0For instance column A has dog, cat,
> > > fish, etc (up to 16 category types) and column B has age. =A0I am try=
ing
> > > to find min age for each category and not include zero.
>
> > > I know this will find the min age of everything
> > > =3DMIN(IF($G$5:$G$40>0, $G$5:$G$40))
>
> > > but i am at a lost for how to incoporate the other criteria
>
> > > any suggestions would greatly be appreciated
>
> thats returning a zero.
> here is what i am using...exchanging dog for a cell regerence
>
> =3DMIN(IF(($G$5:$G$40>0)*($E$5:$E$40=3DE5), $G$5:$G$40))
>
>
>
> > Maybe...
>
> > =3DMIN(IF(($G$5:$G$40>0)*($A$5:$A$40=3D"dog"), $G$5:$G$40))
> > for minimum of dog ages
>
> > entered as an array formula.
>
> > Ken Johnson- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

0
4/24/2009 3:18:16 PM
On Apr 25, 1:18=A0am, calebmich...@gmail.com wrote:
> disregard last posting. =A0didnt see the enter as array line. =A0works
> great. =A0thanks a bunch!!!
>
> On Apr 24, 11:16=A0am, calebmich...@gmail.com wrote:
>
> > On Apr 24, 10:13=A0am, Ken Johnson <KenCJohn...@gmail.com> wrote:
>
> > > On Apr 24, 10:41=A0pm, calebmich...@gmail.com wrote:
>
> > > > Hi
>
> > > > I am trying to exclude zero plus add another criteria in calculatin=
g
> > > > the min value of a column. =A0I want the other criteria to be the t=
ext
> > > > value of a different column. =A0For instance column A has dog, cat,
> > > > fish, etc (up to 16 category types) and column B has age. =A0I am t=
rying
> > > > to find min age for each category and not include zero.
>
> > > > I know this will find the min age of everything
> > > > =3DMIN(IF($G$5:$G$40>0, $G$5:$G$40))
>
> > > > but i am at a lost for how to incoporate the other criteria
>
> > > > any suggestions would greatly be appreciated
>
> > thats returning a zero.
> > here is what i am using...exchanging dog for a cell regerence
>
> > =3DMIN(IF(($G$5:$G$40>0)*($E$5:$E$40=3DE5), $G$5:$G$40))
>
> > > Maybe...
>
> > > =3DMIN(IF(($G$5:$G$40>0)*($A$5:$A$40=3D"dog"), $G$5:$G$40))
> > > for minimum of dog ages
>
> > > entered as an array formula.
>
> > > Ken Johnson- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -

You're welcome.
Thanks for feeding back.

Ken Johnson
0
KenCJohnson (314)
4/24/2009 4:14:22 PM
Reply:

Similar Artilces:

show another column when hovering on a point in a chart
Is it possible to show another column when you hover on a point? I have 3 columns, A, B, and C for a scatter plot I have B and C as x and y coordinates of a point, and I'd like to have Excel show A when I hover on the point. thanks, Wei ...

Change <value> in outlook
We Have Exchange 2003 sp2 and outlook 2003 sp2. I have renamed 2 AD accounts. Changed all display names, accounts name etc with the 2 accounts. When users open outlook and send to renamed account they see account name <oldaccountname> in the autocomplete list. I have cleared nk2 files in profile , but it still shows newaccount <oldaccountname> in drop down box. Is there a way to edit the <oldaccountnam> value of the renamed account? I tried the same thing with a freshly loaded PC. with the same result. Thanks You can delete the nickname files. -- Ed Crowley MVP - E...

Sorting Alphanumeric values in a text field
I'm using Access 2003 for a database for my company. I have a field in a table that has both text and numbers. They are part numbers, for example 21BC124. I kept the field as text because of the text with in the numbers and didn't figure that a numeric field would alow the text. In my part numbers table it sorts correctly (first by number then by letter then by number again), but in my reports and queries there are a few number that sort in the wrong place. Like this... 20D10-3 21BC123 21BC128 22D10 25TD47 21FA101 21FA200 25FA203 38FA601 21FP604 38WS100 I can't quite f...

Passing a variable as a parameter value instead of a literal string to a child report?
In the Navigation section (Jump to URL), I have the following: ="javascript:void(window.open('http://localhost/reportserver?/Reports +Folder/My +Report&rs:Command=Render&StartDate=01/01/2009&FinishDate=12/31/2010'))" This works as designed - a new window pops up with the "My Report" report and the StartDate and FinishDate of 01/01/2009 and 12/31/2010 are passed respectively. But what is the syntax for changing the "01/01/2009" and "12/31/2010" to variables that point to the StartDate and FinishDate parameters of the PAR...

Count unique values
Hi, I've some problems to count unique items (Invoice #) in a pivot table. There is the default solution "Add-a-new-calculated-column" as mentioned on http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't works. My problem is, that these values aren't in a Excel worksheet; it's a external data source - a SQL-Select via DAO/ADO. Because of that, I don't have the possibility to add a new column. Another reason is, that the pivot table should always be dynamic: Group over this field, group over another field - and always show the number of unique...

Why should I upgrade to 8.0?
If you were currently running MBS 7.5 at the moment, are there any compelling reasons to move up to 8.0? Or would you be better off waiting it out for their 9.0 release? If you haven't seen reason enough, you might as well wait. When you do decide to upgrade, you will still have to go through 8.0 as there is noupgrade path from 7.5 to 9. Your VAR will do both upgrades one after the other so there should be no downtime. HS "ElmerT" <elmer.tagarino@gisimplement.com> wrote in message news:%23gx97N6zFHA.2652@TK2MSFTNGP14.phx.gbl... > If you were currently running...

Top values
Dear friends, need your help again please. I have a table: tbl_Plots (PlotID is the primar key - number byte) and tbl_Data (ID is the primary key - autonumber, PlotID related to tblePlots, height - number byte and diameter - number integer). I need to select the 20 bulkiest trees of each plot, i.e. having the biggest diameter. Also, perhaps in a plot less than 20 trees will be present so I will need all of them. Any suggestions? Thanking you in advance, GeorgeCY hi Geroge, George wrote: > I have a table: tbl_Plots (PlotID is the primar key - number byte) and > tbl_Data (ID is...

Using Sumproduct when some of the values are null
I am having a problem using Sumproduct when some of the cells have null values. I will try to describe the problem. Please let me know if you need additional information. Do any of you have any ideas of how I can fix that sumproduct to get it to work? A B C Row Trend Claims 5 Emergency $0 6 Emergency $0 7 Emergency $0 8 Emergency 81.68% $24,444 9 Emergency 35.00% $164,758 10 Emergency 35.00% ...

4.0 Acceleration Software?
Have heard tell of a software "maybe called accelerate" that can be installed on the CRM server, which will increase speed and performance. My clients are complaining of slowness since crm has been installed. They all use Outlook client for desktop, and say there is a definite latency. I've been searching the web, but coming up empty on a software for this. Is it an urban myth or a secret that needs to be be shared? ...

How do I get text to copy from one cell to another ?
Type = in the target cell. MouseClick the cell containing text. Pres Enter key -- Brian ----------------------------------------------------------------------- BrianB's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5 View this thread: http://www.excelforum.com/showthread.php?threadid=27034 ...

Great Plains integration to CRM 4.0
does anyone know if there is integration of great plains with crm 4.0? we have crm 3.0 integrated to great plains 8.0 and want to upgrade both. Hi Shawn, There is no Microsoft connector yet for CRM 4 and GP. There is one for CRM 3 and GP. Other options are to use • some third party tools like Scribe • or use Microsoft BizTalk • or do custom Programming. -- uMar Khan :: MS CRM MVP Microsoft CRM Consultant Email :: imumar at gmail dot com Blog :: http://umarkhan.wordpress.com MVP :: https://mvp.support.microsoft.com/default.aspx/profile/umar.khan "Shawn" wrote: > does an...

Incorporating detail of one sheet into another. #2
There are 2 excel worksheet. I would like to incorporate detail of sheet 2 into sheet1. e.g Sheet2 Contain the following details A B C D E F G Agent1 Agent2 Agent3 Agent4 Agent5 1 Apple Cycle 2 Mango Car 3 Banana Bus 4 Coconut Motor and so on in coloumn c , d , e,f and g. Now what I would like to do is If in sheet 1 If I select Agent 1 all the detail of sheet2 which has column as agent1 should appear exactly as it is in sheet2. And the same thing for Column B, c ...

Import Templates for Dynamics CRM 3.0
Hi all I am trying to use the standard import file into Leads, and it is coming up with data type not valid, length not valid however I am 100% sure it is correct. Have any of you out there got formatted xls files that have the correct data types. field lengths that we could possibly use as a template to see if it is actually my error? Regards Wayne Lockey ...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

CRM 3.0 Email Attachments
I understand the attachments are stored in the DB iteself. I am able to find out which table and fields. I see attachment NAME, MIME TYPE, MIMEID but unable to locate which column in the MSCRM.ActivityMimeAttachment table the content is stored ? Please advice. I am trying to download the attachment from the CRM Database into a folder as a file for a given activityid. ...

Hyper link one column to another
I would like to have my columns hyper link one another. For example: Click on B2 would take you to N2, and vice-versa. Click on C2 would take you to O2... and so on through column j linked to V. Is this possible without having to make the link for each cell? This is a timesheet template and the columns b though J are the hours and N through V are the text comments for those hours. When I copy the template to a new sheet, there will be no data in any column. I would like to be able to enter an amount or formula (=end-start) for time spent and then be able to click on that cell and hyperlin...

Copying Publisher from One computer to another
I want to copy Publisher from my old computer to my new one, but if I can't seem to locate all the files I need to make it work on the new computer. What should I do? You can't, you have to install it from the CD. -- ~~~~~~~~~~~~~~~~~~ Rob Giordano Microsoft MVP Expression "Jasmin" <Jasmin@discussions.microsoft.com> wrote in message news:7EB558F1-525D-47BD-A54B-BC62C410D79E@microsoft.com... |I want to copy Publisher from my old computer to my new one, but if I can't | seem to locate all the files I need to make it work on the new computer. What | shoul...

CRM 3.0 Setup err databases already exist on the specified SQL Ser
ReportServer ReportServerTempDB Question : How to clear this problem? The On-Line Help did not applied as SQL 2005 has been installed on SBS2003 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++ Microsoft SQL Server Reporting Services Cause There are two causes for this error: Microsoft SQL Server Reporting Services is already installed on the specified server. Setup has found a version of Microsoft SQL Server that is not supported by Microsoft SQL Server Reporting Services. Solution One of the following versions of Microsoft SQL Server are required for Microsoft SQL Server Repor...

Deleting Unique Values
How do you delete unique values in a column? I need to filter 7500+ rows to only display duplicate values. The VBA code below will delete the entire row when the value in col. "A" will be uniqe. Consider to make a copy of your entire sheet in order to test the code and see if this is what you need. ------------------------------- Sub Delete_Uniques() LR = Cells(Rows.Count, 1).End(xlUp).Row For R = LR To 1 Step -1 If Application.CountIf(Range("A:A"), Cells(R, 1)) = 1 Then Cells(R, 1).EntireRow.Delete End If N...

Getting right date value
I setup my DTPicker control to be used only as a date control, yet I'm noticing that sometimes it will give back a date AND a time all in the same "value" variable. Since it appears that a variable of type "Date" can give back both a date and time, how can I eliminate the time half of a date value??? I might not be able to exactly control the DTPicker control to give me JUST a date, so I'm just curious what to do if it gives me back both a date & time. thank u Hi, Try this : Dim x as date x = cdate(clng(DTPicker1.value)) &qu...

CRM 4.0: Do notes carry over in lead conversion?
Hi all, I know that in CRM 3.0, when you convert a lead, you lose the notes associated with it. Is this still the case with CRM 4.0? Is there maybe a way to fix that now--mapping relationships?!? Thanks. Hi, Same is the case in CRM 4. Notes of Lead will stay with Lead after conversion to Account or contact. To move them to Account or contact, you need coding. Llugin or workflow assembly will work for you. -- uMar Khan :: MS CRM MVP Microsoft CRM Consultant Email :: imumar at gmail dot com Blog :: http://umarkhan.wordpress.com MVP :: https://mvp.support.microsoft.com/default.aspx/profil...

Office 2004 SP 11.1.0 installation problems
I'm trying to update Office 2004 on my brand new iBook. I installed Office 2004 from the CD without incident. Then I downloaded SP 11.1.1.0 and 11.1.0 (which according to the MS website is to be installed 1st.) When I try to install, I received a message, "An error prevented the update from completing 11002:2,-14" The "Read Me" file indicates several potential explainatins/solutions none of whice are relevant except for uninstalling Office and then reinstalling it. I tried dragging the Office folder to the trash and reinstalled and am having the same problem. I've t...

Making words in a list a value!!
I am trying to create a list that will generate a value in another column once that word or phrase is chosen. Is this possible and how. Exp. In the drop down list I would chose Product, then in the price column the price automatically appears. brco1, Much better than I could ever explain the process. Here's exactly wha you need. http://www.contextures.com/xlFunctions02.html HT -- Case ----------------------------------------------------------------------- Casey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=454 View this thread: http://www.excelforu...

ComboBox passing values from selected
In my form (Items_frm) I have a combo box (Itemcbx) that displays 4 columns (Item, Makebuy, Revision, IsActive) but is bound to the first column. However, I would like to pass the other values from the remaining columns to other fields in my form after the selection. Is this possible? Can someone provide a sample code? You can use the Column() attribute. Me.Itemcbx.Column(1) etc. Note that the index for combo box columns is zero-based so 0 is the first, 1 is the second and so on... Steve "Angel G" wrote: > In my form (Items_frm) I have a combo box (Itemcbx) that displ...

find match then change cell value
In column A I have cells filled with text and in column B I have cells filled with numbers. I need to check if the number in cell C1 equals any of the numbers in column B. If a match is found then I need to change the text in column A to CBO. e.g. Column A Column B Column C aep 5 7 apa 0 gci 59 xto 5000 xle 7 oih 253 ed 8 Since the cell C1 = 7 equals the 7 from column B, I need to change the data in column A from xle to cbo. Is this possible...