MIN within range based on criteria

I have two rows of dates, row A - "Start" date & row B - "Due" date.  In cell 
A1 user can select a start date from the range in row A below.  There will be 
multiple lines that have same "Start" date but the "Due" date could vary.  I 
would like the MIN "Due" date value to appear in B1 based on the 
cooresponding "Start" date user chooses in cell A1.
ie.
A1. user chooses start date /  B1. MIN value row B appears
A2. 12/1/5 B2. 12/4/5
A3. 12/2/5 B3. 12/6/5
A4. 12/3/5 B4. 12/6/5
A5. 12/3/5 B5. 12/5/5
if user chooses start date of 12/3/5 the MIN value within row B range is 
12/5/5.  Can someone help me write a formula for B1.
Thanks, Steven 

0
StevenL (7)
7/10/2005 10:07:01 PM
excel.misc 78881 articles. 5 followers. Follow

9 Replies
912 Views

Similar Articles

[PageSpeed] 14

One way .

Put in the formula bar for B1 and array-enter
(i.e. press CTRL+SHIFT+ENTER):
=IF(A1="","",MIN(IF(A2:A100=A1,B2:B100)))

--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"StevenL" <StevenL@discussions.microsoft.com> wrote in message
news:64C2FDE9-9500-4CB9-9624-8104B61D1651@microsoft.com...
> I have two rows of dates, row A - "Start" date & row B - "Due" date.  In
cell
> A1 user can select a start date from the range in row A below.  There will
be
> multiple lines that have same "Start" date but the "Due" date could vary.
I
> would like the MIN "Due" date value to appear in B1 based on the
> cooresponding "Start" date user chooses in cell A1.
> ie.
> A1. user chooses start date /  B1. MIN value row B appears
> A2. 12/1/5 B2. 12/4/5
> A3. 12/2/5 B3. 12/6/5
> A4. 12/3/5 B4. 12/6/5
> A5. 12/3/5 B5. 12/5/5
> if user chooses start date of 12/3/5 the MIN value within row B range is
> 12/5/5.  Can someone help me write a formula for B1.
> Thanks, Steven
>


0
demechanik (4694)
7/10/2005 11:02:01 PM
If you would sort your data using column A as the primary key ascending and
column B as the secondary dey ascending, then the following in B1 will
do........(ranges based on your sample data, change as required)

=IF(AND(ISNUMBER(A1),A1>=MIN(A2:A5)),VLOOKUP(A1,A2:B5,2,FALSE),"No
acceptable date in A1")

Vaya con Dios,
Chuck, CABGx3


"StevenL" <StevenL@discussions.microsoft.com> wrote in message
news:64C2FDE9-9500-4CB9-9624-8104B61D1651@microsoft.com...
> I have two rows of dates, row A - "Start" date & row B - "Due" date.  In
cell
> A1 user can select a start date from the range in row A below.  There will
be
> multiple lines that have same "Start" date but the "Due" date could vary.
I
> would like the MIN "Due" date value to appear in B1 based on the
> cooresponding "Start" date user chooses in cell A1.
> ie.
> A1. user chooses start date /  B1. MIN value row B appears
> A2. 12/1/5 B2. 12/4/5
> A3. 12/2/5 B3. 12/6/5
> A4. 12/3/5 B4. 12/6/5
> A5. 12/3/5 B5. 12/5/5
> if user chooses start date of 12/3/5 the MIN value within row B range is
> 12/5/5.  Can someone help me write a formula for B1.
> Thanks, Steven
>


0
croberts (1377)
7/10/2005 11:32:09 PM
Thanks Chuck, but relying on others to fill in the data and can expect no 
consistency.  The formula will be for multiple others and not always there to 
explain below.  I need to stay away from index and Vlookup formulas.

Brgds and thanks for the quick response.
Steven


"CLR" wrote:

> If you would sort your data using column A as the primary key ascending and
> column B as the secondary dey ascending, then the following in B1 will
> do........(ranges based on your sample data, change as required)
> 
> =IF(AND(ISNUMBER(A1),A1>=MIN(A2:A5)),VLOOKUP(A1,A2:B5,2,FALSE),"No
> acceptable date in A1")
> 
> Vaya con Dios,
> Chuck, CABGx3
> 
> 
> "StevenL" <StevenL@discussions.microsoft.com> wrote in message
> news:64C2FDE9-9500-4CB9-9624-8104B61D1651@microsoft.com...
> > I have two rows of dates, row A - "Start" date & row B - "Due" date.  In
> cell
> > A1 user can select a start date from the range in row A below.  There will
> be
> > multiple lines that have same "Start" date but the "Due" date could vary.
> I
> > would like the MIN "Due" date value to appear in B1 based on the
> > cooresponding "Start" date user chooses in cell A1.
> > ie.
> > A1. user chooses start date /  B1. MIN value row B appears
> > A2. 12/1/5 B2. 12/4/5
> > A3. 12/2/5 B3. 12/6/5
> > A4. 12/3/5 B4. 12/6/5
> > A5. 12/3/5 B5. 12/5/5
> > if user chooses start date of 12/3/5 the MIN value within row B range is
> > 12/5/5.  Can someone help me write a formula for B1.
> > Thanks, Steven
> >
> 
> 
> 
0
StevenL (7)
7/11/2005 1:21:01 AM
Thanks Max, worked like a charm.
Best Regards, 
Steven 

"Max" wrote:

> One way .
> 
> Put in the formula bar for B1 and array-enter
> (i.e. press CTRL+SHIFT+ENTER):
> =IF(A1="","",MIN(IF(A2:A100=A1,B2:B100)))
> 
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8,  1° 22' N  103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "StevenL" <StevenL@discussions.microsoft.com> wrote in message
> news:64C2FDE9-9500-4CB9-9624-8104B61D1651@microsoft.com...
> > I have two rows of dates, row A - "Start" date & row B - "Due" date.  In
> cell
> > A1 user can select a start date from the range in row A below.  There will
> be
> > multiple lines that have same "Start" date but the "Due" date could vary.
> I
> > would like the MIN "Due" date value to appear in B1 based on the
> > cooresponding "Start" date user chooses in cell A1.
> > ie.
> > A1. user chooses start date /  B1. MIN value row B appears
> > A2. 12/1/5 B2. 12/4/5
> > A3. 12/2/5 B3. 12/6/5
> > A4. 12/3/5 B4. 12/6/5
> > A5. 12/3/5 B5. 12/5/5
> > if user chooses start date of 12/3/5 the MIN value within row B range is
> > 12/5/5.  Can someone help me write a formula for B1.
> > Thanks, Steven
> >
> 
> 
> 
0
StevenL (7)
7/11/2005 1:45:02 AM
Glad to hear that, Steven !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"StevenL" <StevenL@discussions.microsoft.com> wrote in message
news:6EAE1899-008E-49A9-9EFD-32677765953C@microsoft.com...
> Thanks Max, worked like a charm.
> Best Regards,
> Steven


0
demechanik (4694)
7/11/2005 1:49:35 AM
Max, I left one thing out... there are two cells for the user to set 
criteria. A "Start" and "End" date.
A2:A100>=A1
and
A2:A100<=B1

Don't suppose you have a solution?  (thought I'd be able to sort this out 
myself)
Rgds, Steven

"Max" wrote:

> One way .
> 
> Put in the formula bar for B1 and array-enter
> (i.e. press CTRL+SHIFT+ENTER):
> =IF(A1="","",MIN(IF(A2:A100=A1,B2:B100)))
> 
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8,  1° 22' N  103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "StevenL" <StevenL@discussions.microsoft.com> wrote in message
> news:64C2FDE9-9500-4CB9-9624-8104B61D1651@microsoft.com...
> > I have two rows of dates, row A - "Start" date & row B - "Due" date.  In
> cell
> > A1 user can select a start date from the range in row A below.  There will
> be
> > multiple lines that have same "Start" date but the "Due" date could vary.
> I
> > would like the MIN "Due" date value to appear in B1 based on the
> > cooresponding "Start" date user chooses in cell A1.
> > ie.
> > A1. user chooses start date /  B1. MIN value row B appears
> > A2. 12/1/5 B2. 12/4/5
> > A3. 12/2/5 B3. 12/6/5
> > A4. 12/3/5 B4. 12/6/5
> > A5. 12/3/5 B5. 12/5/5
> > if user chooses start date of 12/3/5 the MIN value within row B range is
> > 12/5/5.  Can someone help me write a formula for B1.
> > Thanks, Steven
> >
> 
> 
> 
0
StevenL (7)
7/11/2005 2:39:03 AM
With the start and end dates in A1 and B1,

Try this revised formula in C1 (array-entered as before):
=IF(OR(A1="",B1=""),"",MIN(IF((A2:A100>=A1)*(A2:A100<=B1),B2:B100)))

Adapt the ranges to suit ..
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"StevenL" <StevenL@discussions.microsoft.com> wrote in message
news:D19FF2A7-BB19-4EDC-879C-8D68DA840B3E@microsoft.com...
> Max, I left one thing out... there are two cells for the user to set
> criteria. A "Start" and "End" date.
> A2:A100>=A1
> and
> A2:A100<=B1
>
> Don't suppose you have a solution?  (thought I'd be able to sort this out
> myself)
> Rgds, Steven


0
demechanik (4694)
7/11/2005 4:27:12 AM
Again, worked liked a charm.  Thanks for your help MAX!

Best Regards

"Max" wrote:

> With the start and end dates in A1 and B1,
> 
> Try this revised formula in C1 (array-entered as before):
> =IF(OR(A1="",B1=""),"",MIN(IF((A2:A100>=A1)*(A2:A100<=B1),B2:B100)))
> 
> Adapt the ranges to suit ..
> --
> Rgds
> Max
> xl 97
> ---
> GMT+8,  1° 22' N  103° 45' E
> xdemechanik <at>yahoo<dot>com
> ----
> "StevenL" <StevenL@discussions.microsoft.com> wrote in message
> news:D19FF2A7-BB19-4EDC-879C-8D68DA840B3E@microsoft.com...
> > Max, I left one thing out... there are two cells for the user to set
> > criteria. A "Start" and "End" date.
> > A2:A100>=A1
> > and
> > A2:A100<=B1
> >
> > Don't suppose you have a solution?  (thought I'd be able to sort this out
> > myself)
> > Rgds, Steven
> 
> 
> 
0
StevenL (7)
7/11/2005 6:46:02 PM
You're welcome !
Thanks for posting back ..
--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"StevenL" <StevenL@discussions.microsoft.com> wrote in message
news:A2DE1023-51DF-430B-9661-C364BC678512@microsoft.com...
> Again, worked liked a charm.  Thanks for your help MAX!
>
> Best Regards


0
demechanik (4694)
7/11/2005 10:33:48 PM
Reply:

Similar Artilces:

Subject based filtering of DSN messages
I can't make this work for the vast majority of the DSN messages we get (message undeliverable and such). I have setup rules to filter the different bounce messages that I see in subject lines and some of them work fine. Most do not. The messages the come from postfix (for instance) have mime-encoded notification messages, split in several parts. I've tried matching the Subject: line I end up seeing in Outlook and the subject line that is in the internet headers of the message, which is not what outlook shows me. I think exchange 2003 is recognizing these messages and try...

Calendar Formatting Date Range problem
Hi, I am trying to make a monthly calendar that our administrator can update easily with new dates, and mail out. I've formatted the calendar and it looks great as it is right now for the month of April. However, when I choose "change date range" it reverts back to the original formatting and I have to start all over. I realize this is because I used a "design object". However, if I don't use a design object, then I can't change the date ranges. How do I change the date range on my calendar without losing all the formatting changes I made? Thanks T...

create chart from non adjacent range
I need to know how to do this Select one area, then hold CTRL while selecting the next area, etc. Then run the Chart Wizard as usual. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ "rita" <rita@discussions.microsoft.com> wrote in message news:18C9672C-0EAB-401C-8798-4C51158EAF8B@microsoft.com... >I need to know how to do this ...

Getting row indexes on Range
(I refer to C# code, but answers in VB are welcome) I have a Range in Excel, which includes several cells (the cells the user selected in the Excel sheet). The range might include the following cells A2, B7, G4. This means that the cells might not be connected. If I look at myRange.Cells.Count, it will return 3. If I look at myRange.Row, it will return 2 (if A2 was the first selected row by the user). Now, I need to get the row numbers of all selected rows, so in the above range, I need an int[] of {2, 7, 4}. But I can't see any solution to go through the Cells and get t...

min query
Hi, How do I get the 2nd minimum value and the 3rd minimum from a table after I have done MIN query for the 1st minimum value? SELECT dbo_CON.SC, dbo_CON.SN, Min(dbo_CON.SQ) AS MinOfSQ FROM dbo_CON GROUP BY dbo_CON.SC, dbo_CON.SN HAVING (((dbo_CON.SC)<>49)); Thanks, Hi Jerry, This will give me three different SC SN records. I am looking for same SC SN records that has next min SQ. table SC SN SQ 1 100 1 1 100 2 1 100 3 1 100 ...

Error when no records meet criteria
Hi, I have the following code and receive error 1004 Application defined or object defined error on this line Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<>""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 because there are no records in this instance of the report for "4" in ColB. I've searched and applied code all to no avail. Can someone please tell me how to write code for when this may occur in any of the situations below? Range("b2:b800").Select ...

>> Calendar Control drag to select range
Hi, using MS Access 2003, is it possible to allow a user to drag to select a range of dates and, if 'yes', how do you store the selected date range? Many thanks, Jonathan Not that I know of. Storing a range of dates is quite simple though. You store the start date and the end date and use a calculation to show all the dates between. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Jonathan" <Jonathan@discussions.microsoft.com> wrote in message news:4F22299F-3765-40D2-AA03-67FB42FFC07A@microsoft.com... > ...

Can't get Date Range to work from Form to Report
Hi, I've followed Allen Browne's directions very closely for creating a form for a date range to limit results to that date range on a report and can not get it to work for some reason. I've read many threads on this board which all say the same thing and it seems to work for everyone else. :( Here's what I have: A form with two unbound text boxes called [txtDateBegin] and [txtDateEnd]. The form is called "frmDateRange" and the report is called "Sales Activity Report". The two unbound text boxes are set to "Short Date" format which the user ...

Criteria help
The following sql query returns a good result except some of the enteries do have a business address and I will be using the query to produce labels. What I would like to do is if the BusinessAddress1, BusinessCity, BusinessState and BusinessPostalCode are blank then use the Home Address, HomeCity, HomeState and HomePostalCode. So will I be able to do this using the criteria or do I have to write a sql query. Either way would someone give some help to acheive a good result? Thanks, Richard SELECT [Main Directory].[Sr Pastor], [Main Directory].NoPastorChurch, [Main Directory].[Colle...

Unlocking named ranges
I am working with a spreadsheet created by a former employee. He used named ranges a lot. I am trying to understand the worksheet. When I see an equation in a cell like =Sum(Shipments) I feel I know what it means. But how do I learn what the actual cell reference is ie = Sum(D2:G2).? TIA Insert a new, empty worksheet into the workbook. Then you can use The Insert-> Name-> Paste menu option and click on the Paste List button to get a listing of all the names and the definitions "JohnL" wrote: > I am working with a spreadsheet created by a former employee. He used...

print only range that contain information in excel 2007
i have a large worksheet to cover a large number of possible inserted entries, all cells show blank until entries are copied and pasted in the worksheet. The amount of entries vary. Is there a way to set the workseet to print only the range of cells with information in them ...

Automatic Dynamic Chart Ranges
Hi: I have an XY plot that refers to a set range of values on my worksheet In this case, it is 8 rows long. I chose 8 rows because that is th maximum number of data points that I will ever need. I have set up th chart values so that they are dynamically calculated based on VLOOKUP from other data. Sometimes there are only 2 data points, sometimes or 6. The problem is that no matter how I set up the formulas, Exce does not like the cells without values in them being included in th chart series (I have hardwired it to include all 8 rows). I thought i might ignore a blank ("" in ...

formatting selected characters or numbers in each cell within a range of cells
I am sending this to several Excel newsgroups, because I am not sure which one if the most appropriate. I apologize in advance for the multiple postings. I have MS Excel 2000 (version 9.0.3821 SR-1). How can I format only a selected character or number within each cell in a specific range of cells? For instance if I have a cell containing �1234234�, how can I format it so that only the 2�s are bold or are colored? Obviously, I have a range of cells, and I want to format all the 2�s occurring in any of the cells as either bold or colored. Conditional formatting does not seem to all...

cell to have comma-delimited values based on text
I have a table called "220_reference" with a column name "Part Number" having a sample value of below: 4047122(All Dash no.), 4057222(All Dash no.), 4058222(All Dash no.), 4060122(All Dash no.) The entire value is in one cell representing the "Part Number" column(defined as general type so text I suppose). Simple enough. But what I need to do is take any number that has "(All Dash no.)" after it and search through a column in another table to retrieve any rows that have that number(text) in it. The other table name is "220" with ...

How write blank cells as the criteria "URGENT"
I have learned how to count cells with data according to criteria, bu now part of my criteria is cells that are empty and I don't know how t write. {=SUM((D1:D81="0_F")*(J1:J81="I"))} In the illustration above I need to count the cells that are blank i Column D AND I in Column J I thought this is how I would write it, but it doesn't work. {=SUM((D1:D81=" ")*(J1:J81="I"))} Thanks for any help -- Message posted from http://www.ExcelForum.com How about {=SUM((D1:D81="")*(J1:J81="I"))} ? jeff >-----Original Message-...

Named Ranges
Hi! I created several worksheets that contained huge lists, so I created a dynamic named range. Now that the name has been inserted in my array formulas, my Macro runs very slowly. The Macro unprotects one of my sheets, sorts the data in a table, hides the blank rows and the protects the sheet again. There are no related named range formulas in that worksheet, so I'm not sure why it would be affected. The Macro ran quickly when I had a defined range of data. Would giving a named range like 'Date' and having text in my workbook with 'Date' create conflicts? Could this...

criteria default value?
I've created a query that has a from/to criteria as shown below: >=[From Pre-School Number :] And <=[To Pre-School Number :] The possible numeric range is 1-99, so entering 7 and 7 gives you just Pre-School 7's details, or entering 1 and 99 gives you every Pre-School's details. Is there some way to have the "Enter Parameter Value" boxes come up with a default value already in there (say 1 and 99 respectively), which the user can then typeover if they choose. Thanking you in anticipation. You can create an Access Form [frmPreQuery] with 2 Textboxes (defaulted ...

Excel macro for copying range to another worksheet
On a monthly basis, I would like to copy the completed range (varies from month to month) of a database (Sheet1) to a master list (Sheet3). Once the data has been copied I intend to manually delete the entries of Sheet1 and start anew for the new month =96 for eventual transfer to Sheet3. The idea is to copy each month=92s data at the bottom of the previous months=92 (Sheet3). I followed Excel=92s record macro command but the macro I ended up with is not capable of placing the new data at the bottom of the existing one; it simply keeps overwriting the previous entry. Unfortunately, I don=92t...

Ranges
Hi, I have some records on a spreadsheet, I want to select them with a macro the range is A1 to m1 down to however many records there happen to be? how do I specify this for my excel macro? Thanks, Jim. Jim try this to get the end Dim iLastRow As Long, liLastCol As Long On Error Resume Next iLastRow = 1: iLastCol = 1 With ActiveSheet.Range("A:M") iLastRow = .Find("*", .Cells(1), xlFormulas, _ xlWhole, xlByRows, xlPrevious).Row iLastCol = .Find("*", .Cells(1), xlFormulas, _ xlWhole, xlByColumns, xlPrevious)...

Date range in months from month and year fields
In our database we track consultants work dates in four seperate fields, starting month, starting year, ending month, ending year. Is there anyway to calculate the range of months they have worked with this set up? So if someone started March 2004 and ended July 2007 is there a formula to calculate the 40 months they worked? DateDiff("m",[starting month] & "/" & "01" & "/" & [starting year],[ending month] & "/" & "01" & "/" & [ending year]) "monkeycr84" wrote: > In our da...

Using a named range as a data source for a chart
Hi, I'm trying to build a chart that that has a named range as its data source. The named range refers to a table that is 2x3 and is called geographydata. Is there any way that I can have the source data for the graph to be geographydata? Thanks! Michael You can enter the name, preceded by the worksheet name and exclamation point, for the data range, but Excel will convert this name to its cell address. If you want the chart to be dynamic as the name changes its size, you have to define a name for each series' data. This will not dynamically change the number of series in the ch...

Select Records that fall in an external set of ranges (subquery)
HiI have not done subqueries before so I am a bit confused. I have twotables1 - Phone Numbers - (converted to be numeric) (with other fields forlater) and2 - Exchange codes - multiple entries for code with 3 fields -Exchange name (duplication) - Start Range and End Range.I want to return a list of phone numbers that fall into the range fora certain exchange.For example - MRSPOO has 4 entries in the Exchange Table with 4distinct ranges. 200-299, 307-788, 997-1102 and 2036-6698. I would like to be able to return all the phone numbers for a singleexchange code that I would select (using a query...

sum items in a table based on description
I am interested in summing items is a table based on their description. Is there a formula that will add items together from column based on the contents in another cell reference in the same row? I know a pivot table will do this with some restrictions in the table design. You can use sumif if there is onbly one condition Assume you want to sum values in column C where column B is "x" =SUMIF(B2:B500,"x",C2:C500) Regards, Peo Sjoblom "Hrider" <hrider@yahoo.com> wrote in message news:ejggYE$MFHA.3328@TK2MSFTNGP14.phx.gbl... >I am interested ...

Receive PO based on amount.
Greetings GPLings.. :) Is there any way to receive with invoice , a PO that contain service /non inventoried items based on the amount? Eg: PO amount $100000000 for ONE service item and i would want to receive and invoice only $ 200. The reason i used this huge amount as PO is that even if i used a decimal value to receive the PO as a percentage,it will still not be accurate as GP allows only 5 decimal places. In a simple form, i want to receive based on the amount rather than qty. Cheers.. The only way to do that is to enter the PO for a quantity of 100,000,000 at a unit...

Join based on next closest value (like Excel VLOOKUP)
Trying to do something similar to a VLOOKUP (Excel) in an Access 2003 query. I have the following tables: JOBS Job,Quantity A,96 B,256 C,300 D,4299 COSTS Quantity,Cost 0, $1000 100, $1200 200, $1500 300, $2000 400, $2500 500, $3000 I need a query that takes JOBS.Quantity, looks it up in COSTS.Quantity and find the cost for the NEXT LOWEST quantity. (Example: Job B has a quantity of 256 and the next LOWEST quantity from COSTS is 200 so Job B costs $1500.) The results should be as follows: JOBS.Job,COSTS.Cost A,$1000 B,$1500 C,$2000 D,$3000 This would be ...