Dates and Cell Values

How can I get the minimum value from column C for all 8/24/2009 dates in 
Column A?

I tried some index and match functions but still doesn't work.

Column A   Column B              Column C   Column D
8/24/2009	12:12:56 AM	113	904
8/24/2009	12:52:56 AM	114	908
8/24/2009	1:32:56 AM	114	907
8/24/2009	2:12:56 AM	112	897
8/24/2009	2:52:56 AM	113	902
8/25/2009	12:13:57 AM	82	654
8/25/2009	12:53:57 AM	81	650
8/25/2009	1:33:57 AM	81	650
8/25/2009	2:13:57 AM	81	650
8/25/2009	2:53:57 AM	81	649
8/25/2009	3:33:57 AM	81	647
8/25/2009	4:13:57 AM	81	646
8/25/2009	4:53:57 AM	81	643
8/25/2009	5:33:57 AM	81	643
8/25/2009	6:13:57 AM	81	647

-- 
asdf
0
Utf
1/22/2010 1:36:09 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
751 Views

Similar Articles

[PageSpeed] 12

Try this array formula** :

E2 = lookup date = 8/24/2009

=MIN(IF(A2:A16=E2,C2:C16))

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

-- 
Biff
Microsoft Excel MVP


"beto" <beto@discussions.microsoft.com> wrote in message 
news:11F7A8A3-2F0C-404B-806B-97ED577A45B2@microsoft.com...
> How can I get the minimum value from column C for all 8/24/2009 dates in
> Column A?
>
> I tried some index and match functions but still doesn't work.
>
> Column A   Column B              Column C   Column D
> 8/24/2009 12:12:56 AM 113 904
> 8/24/2009 12:52:56 AM 114 908
> 8/24/2009 1:32:56 AM 114 907
> 8/24/2009 2:12:56 AM 112 897
> 8/24/2009 2:52:56 AM 113 902
> 8/25/2009 12:13:57 AM 82 654
> 8/25/2009 12:53:57 AM 81 650
> 8/25/2009 1:33:57 AM 81 650
> 8/25/2009 2:13:57 AM 81 650
> 8/25/2009 2:53:57 AM 81 649
> 8/25/2009 3:33:57 AM 81 647
> 8/25/2009 4:13:57 AM 81 646
> 8/25/2009 4:53:57 AM 81 643
> 8/25/2009 5:33:57 AM 81 643
> 8/25/2009 6:13:57 AM 81 647
>
> -- 
> asdf 


0
T
1/22/2010 3:44:09 AM
Thanks it helped me a lot!,
Another question, What if I have the same data but, instead of finding just  
8/24/2009, I want to find the maximum value in the following range of dates:  
N1=8/25/2009
N2=8/26/2009
N3=8/27/2009
N4=8/28/2009?

I tried this: 
MAX(IF(AND($C$5:$C$45000=N1,$C$5:$C$45000=N2,$C$5:$C$45000=N3,$C$5:$C$45000=N4,$C$5:$C$45000=N5,$C$5:$C$45000=N6),$F$5:$F$45000))

"T. Valko" wrote:

> Try this array formula** :
> 
> E2 = lookup date = 8/24/2009
> 
> =MIN(IF(A2:A16=E2,C2:C16))
> 
> ** array formulas need to be entered using the key combination of 
> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
> key then hit ENTER.
> 
> -- 
> Biff
> Microsoft Excel MVP
> 
> 
> "beto" <beto@discussions.microsoft.com> wrote in message 
> news:11F7A8A3-2F0C-404B-806B-97ED577A45B2@microsoft.com...
> > How can I get the minimum value from column C for all 8/24/2009 dates in
> > Column A?
> >
> > I tried some index and match functions but still doesn't work.
> >
> > Column A   Column B              Column C   Column D
> > 8/24/2009 12:12:56 AM 113 904
> > 8/24/2009 12:52:56 AM 114 908
> > 8/24/2009 1:32:56 AM 114 907
> > 8/24/2009 2:12:56 AM 112 897
> > 8/24/2009 2:52:56 AM 113 902
> > 8/25/2009 12:13:57 AM 82 654
> > 8/25/2009 12:53:57 AM 81 650
> > 8/25/2009 1:33:57 AM 81 650
> > 8/25/2009 2:13:57 AM 81 650
> > 8/25/2009 2:53:57 AM 81 649
> > 8/25/2009 3:33:57 AM 81 647
> > 8/25/2009 4:13:57 AM 81 646
> > 8/25/2009 4:53:57 AM 81 643
> > 8/25/2009 5:33:57 AM 81 643
> > 8/25/2009 6:13:57 AM 81 647
> >
> > -- 
> > asdf 
> 
> 
> .
> 
0
Utf
1/22/2010 7:44:05 PM
Try this array formula** :

=MAX(IF(ISNUMBER(MATCH(C5:C45000,N1:N4,0)),F5:F45000))

** array formulas need to be entered using the key combination of 
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT 
key then hit ENTER.

-- 
Biff
Microsoft Excel MVP


"beto" <beto@discussions.microsoft.com> wrote in message 
news:5CFEC74C-1E92-47F4-8A62-53A2CF958CF2@microsoft.com...
> Thanks it helped me a lot!,
> Another question, What if I have the same data but, instead of finding 
> just
> 8/24/2009, I want to find the maximum value in the following range of 
> dates:
> N1=8/25/2009
> N2=8/26/2009
> N3=8/27/2009
> N4=8/28/2009?
>
> I tried this:
> MAX(IF(AND($C$5:$C$45000=N1,$C$5:$C$45000=N2,$C$5:$C$45000=N3,$C$5:$C$45000=N4,$C$5:$C$45000=N5,$C$5:$C$45000=N6),$F$5:$F$45000))
>
> "T. Valko" wrote:
>
>> Try this array formula** :
>>
>> E2 = lookup date = 8/24/2009
>>
>> =MIN(IF(A2:A16=E2,C2:C16))
>>
>> ** array formulas need to be entered using the key combination of
>> CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the 
>> SHIFT
>> key then hit ENTER.
>>
>> -- 
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "beto" <beto@discussions.microsoft.com> wrote in message
>> news:11F7A8A3-2F0C-404B-806B-97ED577A45B2@microsoft.com...
>> > How can I get the minimum value from column C for all 8/24/2009 dates 
>> > in
>> > Column A?
>> >
>> > I tried some index and match functions but still doesn't work.
>> >
>> > Column A   Column B              Column C   Column D
>> > 8/24/2009 12:12:56 AM 113 904
>> > 8/24/2009 12:52:56 AM 114 908
>> > 8/24/2009 1:32:56 AM 114 907
>> > 8/24/2009 2:12:56 AM 112 897
>> > 8/24/2009 2:52:56 AM 113 902
>> > 8/25/2009 12:13:57 AM 82 654
>> > 8/25/2009 12:53:57 AM 81 650
>> > 8/25/2009 1:33:57 AM 81 650
>> > 8/25/2009 2:13:57 AM 81 650
>> > 8/25/2009 2:53:57 AM 81 649
>> > 8/25/2009 3:33:57 AM 81 647
>> > 8/25/2009 4:13:57 AM 81 646
>> > 8/25/2009 4:53:57 AM 81 643
>> > 8/25/2009 5:33:57 AM 81 643
>> > 8/25/2009 6:13:57 AM 81 647
>> >
>> > -- 
>> > asdf
>>
>>
>> .
>> 


0
T
1/22/2010 10:23:01 PM
Reply:

Similar Artilces:

Adding time values
Hi to all, I'm trying to add time values together, EG: 1:30 (90minutes) + 0:30 (30 minutes) to return a value of 2:00 Is it possible to do this in Excel? Thanks in advance, John. Yes, just put the two times in separate cells, and add them =A1+A2 -- HTH RP (remove nothere from the email address if mailing direct) "Johnty" <johnmcgibbon@msn.com> wrote in message news:1113600171.db49c715fe9c4e0642762c5471fb6105@teranews... > Hi to all, > > I'm trying to add time values together, > > EG: 1:30 (90minutes) + 0:30 (30 minutes) to return a value of...

Incrementing named cells
Merry Christmas everyone!!! Was wondering if any of you can help me with this. I have a financial model that currently has numerous named cells in the format of XXX_03 ( the 03 is to denote 2003 ) but I am making 2004 projection and therefore am wondering if there was an easy way to sort of copy and paste the names into a new column and automically renaming cells by an increment of 1 to XXX_04. If anyone can help, it will be greatly appreciated! Thank you Justin See one answer 1 minute later -- Don Guillett SalesAid Software donaldb@281.com "Justin" <jsim_hba2003@hot...

Using PERCENTILE with *specific* cells in a column
I am trying to use the PERCENTILE function with specific cells in a column. For example, lets say the column is: A 1 5 2 6 3 7 4 8 Using PERCENTILE on say A1:A3 is easy enough, but I want to do say A1 and A4. I can achieve this by defining a named range such that it would be equal to A1 and A4, but would rather not use named ranges, as the above is a very simplistic example of what I want to do -- using named ranges would require a substantial amount of effort to achieve my end goal. I also realize I could re-order the rows such that I swap A4 with A2, therefore could use perc...

How to draw a regression line on 7000+ dated data with Excel 2007
How to draw a regression line on 7000+ dated data using Excel 2007 Are your "dates" actual Excel dates or just text? With actual Excel dates, pre-2007 versions have no problem doing simple linear regression. To interpret the results though, you need to remember how Excel dates are stored: An Excel date is the number of days since 1900, so the numeric value of today's date is 39736, and the intercept of the regression will be the value of the relationship extrapolated to 30Dec1899 (since Excel mistakenly considers 1900 to be a leap year) "Gordon Lee" wrote: >...

Wrap Text in Merged Cell
Excel 2007 If I merge several (or even 2) cells in a row, I can not get the text to wrap when I hit enter. It works, of course, in a single cell. How can I get the text to wrap in merged cells? I have checked "Wrap text" in the Format Cells dialog box, Thanks, Bob Atkinson Long audible sigh here................. One more victim of "merged cells". Wrap Text works fine on merged cells, but Autofit does not work. You need VBA event code to do that. Here is code from Greg Wilson. Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRwHt As Single Dim cW...

I get wrong dates when i paste from a different sheet into a new s
Hello again, My problem now is when I open an old excel sheet with data on it and try to paste that info on a new sheet I get wrong information. example: I'm trying to copy dates from an excel sheet I have. The dates are for the year 2004. When I copy the data and paste it on a new sheet the dates I now see are 2000. I'm puzzled. What I find wierd is when I have clipboard open the information shows the correct dates but when it's actually pasted on the sheet it shows as 2000. I'm using Excel 2003, but I get the same thing when using excel 2002(xp) Thanks for your help,...

sum a field value in all querys
hello everyone How do i sum a value with another field (always the same one) in all my querys? Later, i would like to change that value to see the result in all querys. How do i do that? Please explain me best you can do cose i'm not an expert on access. Tks in advance Pedro Hi Pedro, What is the data type of the other field (always the same one)? The value you wish to sum it with - what data type is it? For example: if you had a query where one field was called rating and it was a long integer, if you wanted to add the value 4 to this you could create a calculated field in the que...

changed time and date and messed up appointments
Hi I recently went to UK from Australia, then to italy, I must have changed the time zones, and now my appointments are all over the place, Is there a way of getting them back to original times and dates. for the future is there a better way of handling time zones. i also use a pocket pc where i probably changed zones as well. thanks tim See the following MSKB article for a workaround: http://support.microsoft.com/?kbid=197480 -- Jocelyn Fiorello MVP - Outlook *** Messages sent to my e-mail address will NOT be answered -- please reply only to the newsgroup to preserve the mes...

Tab into specified cells
I'm looking to set up a worksheet so that when I open it up I can only tab into specific cells. Is there anyway of doing this? Thanks There are a few way. One is to unlock the cells you want to tab into (Format>Cells>Protection), and then lock the sheet (Tools>Protection>Protect Sheet) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "sho" <anonymous@discussions.microsoft.com> wrote in message news:644601c47543$7c069000$a401280a@phx.gbl... > I'm looking to set ...

Find all cells with a number and mulitply
How can I find all cells in a sheet that are a number greater than zero and multiply each one by a number? IE: the number to multiply by is 1.2 A1 Tom A2 25 A3 0 G12 Bob G13 25 I want to programatically (without using a macro) find cells a2 and g13 only and multiply them by 1.2 Thanks in advance for your help. Jim Jim, As long as you don't have any negative numbers, it doesn't matter about being > 0 (0*n=0) or being text. Try this Put 1.2 in a spare cell and then copy it Select all the cells Goto menu Edit>Pastespecial Click the Multiply option OK out Now clear the c...

Cell comments
I have already created the code using VBA to pull the data from the M Access table. Is there a way to set it up so that when you pass ove the cell it will automatically run my routine -- LA ----------------------------------------------------------------------- LAF's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=965 View this thread: http://www.excelforum.com/showthread.php?threadid=26920 Hi not possible AFAIK as there's no event which got triggered by just moving over a comment field -- Regards Frank Kabel Frankfurt, Germany LAF wrote: > I have alr...

Merging cells #4
I am using an existing worksheet in Excel. Some cells have already been merged. I need to merge 2 more cells and i am unable to accomplish that. The "merge/center button" on the toolbar is not highlighted, so i am unable to merge the needed cells. Any suggestions? Any chance your worksheet is protected? brownk wrote: > > I am using an existing worksheet in Excel. Some cells have already been > merged. I need to merge 2 more cells and i am unable to accomplish that. > The "merge/center button" on the toolbar is not highlighted, so i am unable > to m...

Year + week => first date of week
Hi, I would like Excel to calculate the date for the first day (Monday) in a week.I have my sheet setup like this: Cell = A1 = Year = 2009 Cell = A2 = Week = 32 Cell = A3 =DATE(A3,1,1)+7*B3 this gives me the Excel date = 40038 (2009-08-13 Thursday) this is not correct. Correct Excel date for 2009 week 32 should be 40028 (2008-08-03 Monday). I live in Sweden and we use the ISO week that starts on Mondays (first week of the new year is the first week that contains Thursday). I don't know if it has got something to do with that the formula above does not work. Above formula is the one I ...

Sometimes can see all text in a cell, sometimes not.
Hello On Sheet1, I can see all text in cells that have lots of text in them, but have cells to the right that are empty. On Sheet 2, I pasted the following formula into cell A1, and copied this into all of the cells on Sheet2: =IF(Sheet1!A1=0,"",Sheet1!A1) Now I can't see all of the text in a cell, even if the cell to the right of it is empty. Is there anything that can be done? I am copying these cells and pasting them into Word as a picture. Thanks! Its because if you type text into a cell it will spill over into empty cells so it can be read. If you then enter in a...

Date problem #6
I have a column containing 4/21/2004 10/7/2003 5/5/2004 Which I need to convert to UK dates. because of the variables(4/, 10/) I am unable to use mid etc in seperate columns. also =TEXT(f2,"DD/MM/YY")fails to work. Hi are these values 'real' date values. If yes a simple format should do -- Regards Frank Kabel Frankfurt, Germany "Gerry" <Gerry.Briant@goodrich.com> schrieb im Newsbeitrag news:9a4601c486b0$01ac1f40$a501280a@phx.gbl... > I have a column containing > 4/21/2004 > 10/7/2003 > 5/5/2004 > Which I need to convert to UK dates. > bec...

Validation Default Value
I'm using a validation list to populate cell B7 on my worksheet. The choices on this list are dynamic and change based on the value in cell H6. Cell B6 then shows a VLOOKUP value based on the contents of cell B7. The problem is that I often need to change the value of H6 and when I do so it leaves the previous value in cell B7 until a new choice is made from the drop-down list. Is there a way to change the value of cell B7 to be either a blank or maybe make it default to the first choice on the validation list? Thanks for your help. Why would you want to change the value of B7 until...

Cell sizing
Hi everyone, I'm recently new to all of this so I hope this isn't a stupid question. Before using Excel, about 7 or 8 years ago, I used Corel Quattro pro. I was able to set the cell heights and widths in mm, cm , inches, points, etc., so that when I printed a section of a sheet it would be a specific dimension. I haven't been able to do this with Excel. Thanks for your help. Only MacXL 2004 allows you to set height and width directly in inches/cm/mm. Both platforms allow you to set row height in points. Column widths can be set in the number of widths of the zero character in...

Picklist Values start at 200,000?
Has anyone else come accross an issue where, after upgrading to CRM 4, any new picklist values you enter begin with the number 200,000? I have a Lead Source picklist that left off at number 12 in CRM 3.0. When I went into the attribute to add another item (which should have been number 13), it began with number 200,000.??? Thanks. AMCK This is normal behavior in CRM 4 - the custom values start there in a model similar to NAV, AX, etc. That way there is no conflict of new SYSTEM attributes (until they get to 199,000 new attributes that is :) ) on upgrades, sps, etc. If you want a clea...

formula field: don't display anything when value is zero (using ;"
hello, I read this under the topic "How to get a formula field to total an entire table column, even if some cells in the column contain text or are blank" on the MVP-site and can't get it to work. I'm interested in the final bit, using ;"" Does anyone know how to do this? <start quotation>... Then in the total cell, press Ctrl+F9, and within the field braces {}, insert the following formula: { SUM(Table1 F:F) \# "#,##0.00;- #,##0.00;''" } “Table1” in the formula refers to the name of the bookmark you've marked the ...

Average Cells with actuall numbers
Hey all, Hopefully this is a simple one. I'm SUMing a column: =SUM(L8 + L12 + L16) At times, some of these cells might be empty. Can I mod this equation to check for an empty cell? What I'm doing is averaging the numbers in this column by the number of cells that actually have numbers in them. (in this case it would be the sum of L8 + L12 + L16 divided by 3) If, let's say, L8 was empty, I need to divide the total by 2. Make any sense? Thanks for any help! -b Hi Bradley couple of things =SUM(L8+L12+L16) is quite an awkward way of writing either =L8+L12+L16 or =SUM(L8,...

autopopulate sl.no.based on a cell value
I have cell value in work sheet based on a function and it changes accordingly.Based on this cell value I want to populate a serial numbers in a column.suppose c1=15,Iwant to populate from d10:d100 serial number as 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 upto d24 only rest should appear as blank.Iwant this funtion as array that means d10:d100= formula.maximum cell value is around 90.Any idea to get this result. In D10 enter: =IF(ROWS($1:1)<=$C$1,ROWS($1:1),"") and copy down to D100 Hope this helps Rowan TUNGANA KURMA RAJU wrote: > I have cell value in work sheet based on a ...

Searching Multiples Words in cells and returing it in adjacent cell
Hi All, I am new to excel So i have some dat where i need to find and write the manufacturer name,color,Size,material and etc in fifferent cells so i am searching for a formula to search so all the above things and write it in the adjacent cells...for example if the cells has the sentance "Size :Only One Size Bust:92~102CM Across Shoulder:38CM Sleeve Length:59CM Length:75CM Material :Polyester Cotton".so it sould find if the material is Polyester cotton,Cotton,Kint,Sweater or Chiffon and write it wherever needed and same for the other search category. I would be very thankful to y...

Trying to total the column values in my chart?
Hi !! I am using the Stacked Column with a 3-d visual effect chart. I am charting 6 months worth of numbers with 5 values in each column. I would like to add up the totals in each column of my chart to make a heading (or something). Can this be done? Thanks ! K8T Hi, The last section of this page shows you how to add a total to a 3-d stack. http://www.andypope.info/charts/StackColTotal.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "K8T" <K8T@discussions.microsoft.com> wrote in message news:35E21028-5057-4B86-9725-2324FF5DA718@micr...

Shading a cell using an if statement
Hi Group, What I am trying to do is to shade a cell when an arguement is true. if(a3=10,shade cell,don't shade cell) thanks a bunch for any help!! Hi this is not possible with formulas as they can only return values but not change formats. The only way would be to use VBA and create an event procedure. e.g. something like the following (this code goes into your worksheet module): Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub With Target If .Value = 10 Then .Interior.ColorIndex = 3 End If End With ...

Opening new form in add mode with preset values
I'm currently working on a personal database to manage family-tree type data and have come across a problem. To note off the bat, I'm very new to Access and am essentially teaching myself as I go by picking apart template databases and playing with my own "test" databases. I only yesterday figured out that most of my hitches now require macros or visual basic coding so I've been trying to learn that too. I figured out how to have a new form open to a related record, and how to have a new form open in add mode, but I'm not sure how to have it open a new form in add mo...