Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-22-2020, 09:32 PM
oscarlimerick oscarlimerick is offline Excel and cell contents Windows 8 Excel and cell contents Office 2013
Advanced Beginner
Excel and cell contents
 
Join Date: Jul 2020
Posts: 57
oscarlimerick is on a distinguished road
Default Excel and cell contents

I have a huge spreadsheet with lots of numbers and I have a cell using the =min(c:c)
This cell correctly returns the value of the minimum cell in column c, which is 7, but I need a way to find out where in column c what row) the cell with this value is located. I finally found it in cell c69 by using the find command and searching for the value 7, but I'm thinking there must be an easier way, some way for excel to return the source cell for which a function matches up. ctrl F2 doesn't seem to work. Any ideas how to do this? many thanks
Reply With Quote
  #2  
Old 07-22-2020, 10:00 PM
Purfleet Purfleet is offline Excel and cell contents Windows 10 Excel and cell contents 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

As long as the min is unique you can use =Match(min(C:C),C:C,0) to give you the row number.


However i would ask why you need to know the location of the Min number, is it for another formula?
Reply With Quote
  #3  
Old 07-22-2020, 11:17 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Excel and cell contents Windows 7 64bit Excel and cell contents 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

Does your range start in row 1?
Reply With Quote
  #4  
Old 07-23-2020, 10:57 AM
oscarlimerick oscarlimerick is offline Excel and cell contents Windows 8 Excel and cell contents Office 2013
Advanced Beginner
Excel and cell contents
 
Join Date: Jul 2020
Posts: 57
oscarlimerick is on a distinguished road
Default

Actually it starts in row 4 but I could easily make it start in row 1
Reply With Quote
  #5  
Old 07-23-2020, 11:10 AM
oscarlimerick oscarlimerick is offline Excel and cell contents Windows 8 Excel and cell contents Office 2013
Advanced Beginner
Excel and cell contents
 
Join Date: Jul 2020
Posts: 57
oscarlimerick is on a distinguished road
Default

Quote:
Originally Posted by Purfleet View Post
As long as the min is unique you can use =Match(min(C:C),C:C,0) to give you the row number.


However i would ask why you need to know the location of the Min number, is it for another formula?
Thanks, I tried this formula and it returned a value of row 69 so it appears to work, but I'm wondering what will happen if I have 2 cells in column c with a value of 7? The reason I need to know the row location of the min number is because my spreadsheet is a bunch of numerical values in column c that equate to a related date in column a. So I want to know what date my minimum value of 7 was achieved. your formula appears to achieve this, at least it does if the value 7 in column c is unique.
Reply With Quote
  #6  
Old 07-23-2020, 11:01 PM
ArviLaanemets ArviLaanemets is offline Excel and cell contents Windows 8 Excel and cell contents Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

In case there never will be several rows with same minimum number on same earliest date, this will work. You need a helper column (which you can hide). The result is calculated by Named Value (from Formulas menu, activate Name Manager to see it), and instead row number (which would need an additional helper column), it returns the date of minimum value.

When there is the possibility having several (n) rows with same date (the result of current Named Value will be MinDate*n then), then you have to modify Named Value formula - you have to count such records, and divide current formula with result of count.
Attached Files
File Type: xlsx MinValueDate.xlsx (10.9 KB, 9 views)
Reply With Quote
  #7  
Old 07-23-2020, 11:53 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Excel and cell contents Windows 7 64bit Excel and cell contents 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

See attached
Be aware the solution uses an array formula
The MINIFS function will also do depending on your XL version
Attached Files
File Type: xlsx MinDate.xlsx (10.0 KB, 6 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel and cell contents Copy a cell's contents across many cells cloa513 Word Tables 13 09-19-2016 11:11 PM
Excel and cell contents Selection from cell with list contents autopopulates contents of another cell markharper80 Excel 3 02-16-2015 04:18 PM
Excel and cell contents Assign the value of a cell as a cell reference of another cell in Excel 2010 - How to? bharathkumarst Excel 7 10-13-2014 10:25 AM
Can anyone here tweek this macro for renaming Excel files based on a cell's contents? chrisd2000 Excel Programming 6 07-01-2014 01:53 PM
Excel and cell contents cut&paste cell contents AND formatting cglenn Word Tables 2 08-24-2011 07:04 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:46 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