Microsoft Office Forums Excel and cell contents
 Register FAQ Search Today's Posts Mark Forums Read

#1
07-22-2020, 09:32 PM
 oscarlimerick Windows 8 Office 2013 Novice Join Date: Jul 2020 Posts: 5
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
#2
07-22-2020, 10:00 PM
 Purfleet Windows 10 Office 2019 Advanced Beginner Join Date: Jun 2020 Posts: 47

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?
#3
07-22-2020, 11:17 PM
 Pecoflyer Windows 7 64bit Office 2010 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,423

Does your range start in row 1?
#4
07-23-2020, 10:57 AM
 oscarlimerick Windows 8 Office 2013 Novice Join Date: Jul 2020 Posts: 5

Actually it starts in row 4 but I could easily make it start in row 1
#5
07-23-2020, 11:10 AM
 oscarlimerick Windows 8 Office 2013 Novice Join Date: Jul 2020 Posts: 5

Quote:
 Originally Posted by Purfleet 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.
#6
07-23-2020, 11:01 PM
 ArviLaanemets Windows 8 Office 2016 Expert Join Date: May 2017 Posts: 575

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
 MinValueDate.xlsx (10.9 KB, 5 views)
#7
07-23-2020, 11:53 PM
 Pecoflyer Windows 7 64bit Office 2010 Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,423

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

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post cloa513 Word Tables 13 09-19-2016 11:11 PM markharper80 Excel 3 02-16-2015 04:18 PM bharathkumarst Excel 7 10-13-2014 10:25 AM chrisd2000 Excel Programming 6 07-01-2014 01:53 PM 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.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top