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 39880 articles. 2 followers. Follow

5 Replies
514 Views

Similar Articles

[PageSpeed] 34

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:

Open subfolders of another user's Inbox
We use Outlook 2000. I have configured things to that person A can access person B's Inbox... but how do they access the various subfolders of the Inbox? Thanks. These settings must be configured by B on each subfolder. Also see http://www.howto-outlook.com/howto/permissions.htm -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -What do the Outlook Icons Mean? -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Invisible" <void@dev.null> wrote in message news:e2UGLJW%23EHA.1564@TK2MSFTNGP09.phx.gbl... > We use Outlook 2000....

How to choose maximum value
Hi, I am wondering if anybody can help me with this. I need to take some information from Table 1 to a new table which I am working on. For example, for "apple" in column A, I would like to find the highest number (30) in Column B and return the correspnonding text in Column C (a2) to the new table. Table 1 columnA ColumnB ColumnC apple 2 a1 apple 30 a2 apple 15 a3 apple 6 a4 apple 12 a5 apple 9 a6 banana 3 b1 banana 10 b2 orange 2 o1 orange 1 o2 cherry 10 c1 peanut 20 p1 peanut 50 p2 peanut 25 p3 tomato 2 t1 tomato l t1 t...

#value error
Hi, In the cell B9 the value is 01/09/2006 In the cell C9 i enter the formula =IF(ISBLANK(B9),"",DATE(YEAR(B9),MONTH(B9)+1,DAY(B9))) In the cell D9 i enter the same formula =IF(ISBLANK(C9),"",DATE(YEAR(C9),MONTH(C9)+1,DAY(C9))) It works fine. But if i delete the value in B9, cell c9 displays blank. But cell D9 displays #value error. Is there any way to supress this error and display balnk value in D9. Any help appreciated. Suresh -- mtpsuresh ------------------------------------------------------------------------ mtpsuresh's Profile: http://www.excelforum.co...

Setvalue property to default value
I'm Trying to run macro for set property to set the object to default value related to another object in sub-subform, but I getting the following message, “ you trying to run procedure doesn’t contain the automation object. etc Yousoft, Can you please give the exact details of the macro. I.e. the Actions, and relevant Arguments. Thanks, that will help us to understand. -- Steve Schapel, Microsoft Access MVP Yousoft wrote: > I'm Trying to run macro for set property to set the object to default value > related to another object in sub-subform, but I getting the followi...

display value in combo box
I populate a combox1.rowsource = value1. How do I display value1 in the combo box? I can see it in the dropdown, but how do I display it? Thanks. Kou Do you have it bound to a field in your table? -- KARL DEWEY Build a little - Test a little "Kou Vang" wrote: > I populate a combox1.rowsource = value1. How do I display value1 in the > combo box? I can see it in the dropdown, but how do I display it? Thanks. > > Kou On Thu, 13 Dec 2007 09:19:02 -0800, Kou Vang wrote: > I populate a combox1.rowsource = value1. How do I display value1 in the > combo b...

need help sorting text by trailing Numbers Value
I am looking for some help sorting a text field (PartNumbers) alphabetically but correctly depending on the value of the ending few charachters if theyre numbers.. Here is some sample data an960pd10 an960pd300 an960pd6 i would like it sorted like this an960pd6 an960pd10 an960pd300 any ideas? Thanks Barry I looks like your values have a fixed length to the left of the numbers. If this is true, you can use the following in the Sorting and Grouping dialog expression: =Left([PartNumbers],7) =Val(Mid([PartNumbers],8)) -- Duane Hookom Microsoft Access MVP ...

MIN formula help
Hello All, I want to know what the Max and Min numbers are using the following formulas. The Max formula seems to work without any problem, however it's the Min that I have a problem with. =Max(A2:A10) =MIN(A2:A10) If I only have numbers in cells A2:A8 and the lowest number is 150, then in that cell that I have the following MIN formula in should show me 150. Instead it's blank because I still have two remaining cells in my range (A9 and A10). How can I fix this. Any and all help is greatly appreciated.. Hi! MIN/MAX ignore empty cells. >Instead it's blank because I ...

Adding default value to pre-existing table
I am working within a database that I created using a downloaded text file. I have to add a column to the table that will contain the same value in each field. Is there a way to add this default value to the preexisting table without keyboarding it in? Thanks in advance for any help. Create a select query on that table and field. Run it and see what it return. Next change this query into an Update query. Put in the value that you want into Update To. If you don't want to overwrite any existing data, just update empty records, put Is Null in the criteria. -- Jerry Whit...

copy range of unique value
Hi, i use Excel 2007 and i have a strange behavior with a particular thing that i do. quite often i have list of values with several times the same values. e.g.: 2,3,8,5,4,3,9 from cell A1 to A7 when i use the function: Data => Sort & Filter => Advanced => Copy to another location, list range $A$1:$A$7, copy to $D$1 and checked Unique records only. i correctly get 2,3,8,5,4,9 it means second times that 3 is found, it is removed from the new list. i have also a good result with only alpha value and only numerical values. my main problem is when cells have ...

LIST out all the data according to the "Criteria"
There are 3 data sets are assumed running in A6, in B6 and in C6 down, viz.: In A6 down is data of "date": 1-Jan-07, 1-Jul-07, 1-Nov-07, 1-Apr-08 and 1- Oct-08 In B6 down is data of "category": Revenue, Revenue, Revenue, Cost and Cost. In C6 down is data of "US$": US$1,200, US$1,250, US$3,000, US$450 and US$550. Another set of data is the "criteria": In cell G7 is the "1-Jan-08" 【it is Starting Date】; in cell G8 is the "1-Dec-08"【it is Ending Date】and in cell G9 is the "Cost" 【it is Category】. I know using the function &...

Make a text box expand or shrink based on size of the value
I am working on a report in design view. Is there a way to have a text box get longer or shrink according to the value? For example, one of the headers is "Operations" while another header is "Resource Integration." ***Since the text box has a blue fill, there will be empty space within the text box if I make it large enough to fit the longest word. Therefore, I want the text box to expand or shrink as necessary. Any input is appreciated. Thanks! Glen Set the 'Can Grow' property to Yes. -- Build a little, test a little. "*G...

needs double value in string format
I have a double variable Dim d as double. d gets values stored as 2010031266671939.0 I need this value in string. If I use d.tostring(), it provides in E+ format. I need this double value in string without E+ format. If I use d.Tostring("#.0"), it rounds off the double value & provides as 2010031266671940.0. I need the value as such. Can anyone help me -- Thanks & regards, V.Vallikkannu Project Leader Chella Software Private Limited | Mobile : <9944254599.> | Off: +91 452 4262000 | www.chelsoft.com This e-mail and any files transmitted wit...

vLookup, Look up Value
Hi, I have problems when the look up value in the vLookup formula is a date, and the cell has a differente format. For example, I specify 03/03/10 as the look up value, but the cell has short date format, so the real value in the cell is 03/03/2010. As a result, the formula displays #N/A. How can I solve this? Thanks in advance Regards, Emece.- It would help to see your VLOOKUP() formula. But I suspect it may look something like: =VLOOKUP("03/02/10",Sheet2!A1:B99,2,FALSE) and in Sheet2, column A you actually have dates. So the text you've entered ( ...

Backcolor value in hex
I want to get the hex value of a cell's backcolor, so I can replicate the same color in Access. In VBA, I try to derive this by using Hex(sheet.range(cell).interior.color). I get a hex value, but when I try it in Access, it displays a different color. Am I grabbing the correct property from the cell in Excel? Or, is there another way I can derive the hex value? Thanks in advance. What version of Excel/Access are you using 2003 or 2007? I'm believe you can set the color using HSL or RGB scales, which apply to both Excel and Access (at least in 2003). -- If this helps, pl...

test for value and return position
I am trying to accomplish two things. 1) I want to test for the existance of a value in a range of number that varies from day to day. 2) I want to return the cell reference of the data obtained in question (1) Any IDeas? I though of using something akin to H2:INDEX(H:H,COUNT(H:H)+1)) but I think I'm not on target here Jeff On Fri, 10 Dec 2004 02:54:28 GMT, "Buster" <bubs@gmail.com> wrote: >I am trying to accomplish two things. >1) I want to test for the existance of a value in a range of number that >varies from day to day. > >2) I want to return th...

Return values from a list that sum to a known value
Is it possible to return values from a list that sum to a known product, for example: 10 1 2 7 6 9 The returned list would be 9,1 and 7,2,1. Thanks. ...

Check for duplicates with multiple criteria
My table contains a field labled "Case Number" and another field labled "Date Completed". I have created an input form and included code to check for duplicate case numbers. A msgbox appears with a warning. I would like the code to check for a duplicate case number and then check the "Date Completed" field to see if it is blank. If it is blank I would like the existing record to open. If the date completed field is not blank I would like to continue entering data in the form to create a new record. The code I am currently using is: Private Su...

Hide rows with zero value?
just wondering how I would be able to hide rows on sheets if some cells are blank? the cells contain formulas they are only blank because `zero values` in >tools>options>view is unchecked. I have a sheet set up to SUMIF values from about 16 other sheets, and it returns the values depending on 72 corresponding codes, I would like to hide any of the 72 rows of data that are blank. the values returned will change on a week to week and daily basis, so I would like to be able to use the same design for each new week, hope that makes sense. Hi try something like the following: Sub hide...

Charts and values
Hi All I have an excel column chart with a range of values. Some of the values are so low compared with the main figures (eg. 92% against 0.07%). Are there any suggestions to make the chart more presentable. I have tried adding the value and series name to the column but it looks a bit messy. Ideally I would like a little sub chart showing the lesser values. Is this possible? Any suggestions, ideas or comments appreciated Cheers Rexmann Try http://www.contextures.com/charts.html. This site contains links to other sites with chart tips and techniques including stacking charts. &quo...

formula works when referring to one cell but not to another
I'm sure there is a simple explanation but I can't come up with it. The following is the formula I am using: =IF(OR(X2="roll not cut",X2="cut not roll"),"OK to process", " ") Basically, I am using this with a spreadsheet I receive from another user. I am just trying to automatically mark anything that says 'roll not cut' or 'cut not roll' as 'OK to process' leaving any other comments showing as a blank (to be researched by me later) This formula works fine if I use it to reference another cell that I personally ty...

determine which cell a value is returned from
How to determine which cell a value is returned from. e.g.=MAX(D6:CC280) returns 525 - How do I find the cell where it occurs? Hi one way =CELL("address",INDEX(D6:CC280,MATCH(MAX(D6:CC280),D6:CC280,0))) Cheers JulieD "curiousg" <curiousg@discussions.microsoft.com> wrote in message news:07BEB420-D231-4A8A-A320-981F9F09F7C1@microsoft.com... > How to determine which cell a value is returned from. e.g.=MAX(D6:CC280) > returns 525 - How do I find the cell where it occurs? One way: =ADDRESS(MAX(IF(MAX(D6:CC280)=D6:CC280,ROW(D6:CC280))),MAX (IF(MAX(D6:CC28...

Min bal requirement
My checking account has a minimum balance requirement of $250 to waive the service charge fees, so I want to keep the minimum in the account. Any recommendations on how to balance to the statement? Should I deduct the $250 from the check register & try to remember this every month when I balance to subtract the $250 from the bank's balance? Is there an easier way? Thanks. =?Utf-8?B?S2VsaUI=?= <KeliB@discussions.microsoft.com> wrote on 08 Sep 2007 in group microsoft.public.money: > My checking account has a minimum balance requirement of $250 to > waive the servic...

Lookup based on matrix values
I am struggling to develop one of those "copy down" functions that can produce a set of values. In this simplified example, the formula needs to tell me the name from column A if there is a value in the remaining array (B1:D5). Dave 1 2 3 Bob 1 Sarah 3 George 2 Sally 1 So the first instance of the formula would produce "Dave", the second would produce "Bob", the third ""George", the fourth is "Dave" again, followed by "Sarah", etc. Yes, I need i...

how do i convert Min:Sec to Min.sec for example 68:43:00 = 68.43
how do i convert Min:Sec to Min.sec for example 68:43:00 = 68.43 (68 minutes and 43 seconds) In article <5F13B001-9B6B-4D5D-BD81-ACAAF5799CF6@microsoft.com>, "=? Utf-8?B?dmVsZSBQaGFudA==?=" <vele Phant@discussions.microsoft.com> says... > how do i convert Min:Sec to Min.sec for example 68:43:00 = 68.43 (68 minutes > and 43 seconds) > =MINUTE(C31)+SECOND(C31)/100 where C31 contains the time in hh:mm:ss. Note that XL may format the cell containing the formula with a time format. If so, change it to 'General'. -- Regards, Tushar Mehta www.tushar-m...

Ordering the chart by value
Hi I have a stacked column graph which shows me how many people have been sick over the month and for what reason. The months are on the horizontal axis with the reason creating the stacked columns. Is there anyway of ordering the stacked columns by values ie the greatest used reason code is always at the bottom and the least used at the top. I am currently struggling with it as each month the order will change and wasnt sure if the graph can be ordered like that. -- Thanks Ruth ...