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
Novice
Excel and cell contents
 
Join Date: Jul 2020
Posts: 5
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
Advanced Beginner
 
Join Date: Jun 2020
Posts: 47
Purfleet is on a distinguished road
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 offline Excel and cell contents Windows 7 64bit Excel and cell contents Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,423
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
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
Novice
Excel and cell contents
 
Join Date: Jul 2020
Posts: 5
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
Novice
Excel and cell contents
 
Join Date: Jul 2020
Posts: 5
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: 575
ArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really niceArviLaanemets is just really nice
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, 5 views)
Reply With Quote
  #7  
Old 07-23-2020, 11:53 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Excel and cell contents Windows 7 64bit Excel and cell contents Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,423
Pecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to allPecoflyer is a name known to all
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, 2 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 - Senior Forums

All times are GMT -7. The time now is 02:59 AM.


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