Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-07-2020, 04:37 PM
oscarlimerick oscarlimerick is offline Request for help with a formula Windows 8 Request for help with a formula Office 2013
Advanced Beginner
Request for help with a formula
 
Join Date: Jul 2020
Posts: 57
oscarlimerick is on a distinguished road
Default 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.
Attached Files
File Type: xlsx SAMPLE.xlsx (10.2 KB, 8 views)
Reply With Quote
  #2  
Old 08-07-2020, 11:40 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Request for help with a formula Windows 7 64bit Request for help with a formula Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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?
Attached Files
File Type: xlsx Copy of SAMPLE.xlsx (10.2 KB, 7 views)
Reply With Quote
  #3  
Old 08-08-2020, 08:13 AM
Purfleet Purfleet is offline Request for help with a formula Windows 10 Request for help with a formula Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

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()))
Attached Files
File Type: xlsx SAMPLE_purfleet.xlsx (11.3 KB, 5 views)
Reply With Quote
  #4  
Old 08-08-2020, 10:35 AM
oscarlimerick oscarlimerick is offline Request for help with a formula Windows 8 Request for help with a formula Office 2013
Advanced Beginner
Request for help with a formula
 
Join Date: Jul 2020
Posts: 57
oscarlimerick is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Request for help with a formula 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
Request for help with a formula A Formula Request Planner18 Project 8 01-10-2017 01:16 PM
Formula Help Request OTPM Excel 16 12-16-2013 01:57 PM
Request for help with a formula Formula help request JAMS Excel 2 04-06-2012 10:14 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:25 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft