#1
|
|||
|
|||
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
|
|||
|
|||
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
|
||||
|
||||
Does your range start in row 1?
|
#4
|
|||
|
|||
Actually it starts in row 4 but I could easily make it start in row 1
|
#5
|
|||
|
|||
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
|
|||
|
|||
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. |
#7
|
||||
|
||||
See attached
Be aware the solution uses an array formula The MINIFS function will also do depending on your XL version |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Copy a cell's contents across many cells | cloa513 | Word Tables | 13 | 09-19-2016 11:11 PM |
Selection from cell with list contents autopopulates contents of another cell | markharper80 | Excel | 3 | 02-16-2015 04:18 PM |
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 |
cut&paste cell contents AND formatting | cglenn | Word Tables | 2 | 08-24-2011 07:04 AM |