#1
|
|||
|
|||
Request for help with a formula
Can somebody please help me with Excel? I have attached a spreadsheet called sample.xlsx. It is a huge spreadsheet with a bunch of dates in columns A and B, and a related numerical value for each date in column C. The actual spreadsheet is much larger but for brevity sake I have only filled in the month of August on the attached sample. Cells F3,F4, and F5 tell me the minimum, maximum, and average number in column c, based on the formulas:
F4 is =MIN(C:C), F5 is =MAX(C:C), and F5 is =AVERAGE(C:C) • I am also able to display in which row of column C the maximum and minimum values are found using: H3 is =MATCH(MIN(C:C),C:C,0) and H4 is =MATCH(MAX(C:C),C:C,0) • What I wish to do also though, is display not only the row of column C where the minimum and maximum values are found, but also the actual associated date, I want these to display in cells J3 for the minimum and J4 for the date for the maximum value. In other words, I want cell J3 to display Aug 8 and I want cell J4 to display Aug 24. Any idea how I can do this? Many thanks. |
#2
|
||||
|
||||
1 Be aware that the result will not be a real date
2 Avoid manual alignment as much as possible. By default text is left aligned and numbers right aligned in cells it helps when you get text looking like numbers 3 Is the attached what you need? |
#3
|
|||
|
|||
If you are keeping the row number in you spreadsheet you can make the formula a bit easier and then wrap it in Datevalue to get a proper date
=DATEVALUE(DAY(INDEX(B:B,H3,0))&INDEX(A:A,H3,0)&YE AR(TODAY())) |
#4
|
|||
|
|||
Yes, the formulas you provided are precisely what I needed to do. When I get time I will try and decipher the content of the formulas you provided to determine why they work. Thanks for the quick reply
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Hyperlink request | dbalaam | Word | 2 | 12-23-2017 04:54 AM |
MS Outlook 2010 custom form - validation or formula to request user to check a checkbox | emsa | Outlook | 0 | 09-08-2017 09:37 AM |
A Formula Request | Planner18 | Project | 8 | 01-10-2017 01:16 PM |
Formula Help Request | OTPM | Excel | 16 | 12-16-2013 01:57 PM |
Formula help request | JAMS | Excel | 2 | 04-06-2012 10:14 PM |