|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
for visible cells (filtered), how find value lowest than 4.00 and return the cell number
Hi All,
i have a table with with a column that has numbers like: 10 9.22 7.33 5.44 4.67 3.12 2.10 1.90 and i want to be able to find the lower value bigger than 4 and to do that i can use the formula: =MIN(IF(A2:A8>4,A2:A8)) But i am having two issues that the moment: 1- how can i make this formula work only for unhidden cells? 2- how can i return the line/column of that cell? Thanks and Advance. DC |
#2
|
|||
|
|||
https://exceljet.net/formula/sum-vis...-filtered-list
https://exceljet.net/excel-functions...total-function Smallest # =MIN(105,A1:A44) Row # =MATCH(MIN(105,A1:A44),A1:A44,0) |
#3
|
|||
|
|||
Thanks for answering.
I tried exactly what you suggested. Applied both formulas Smallest # =MIN(105,A1:A44) = 1.90 Row # =MATCH(MIN(105,A1:A44),A1:A44,0) = 8 But when i try to hide a few lines like the line that has 1.90, the result is the same Smallest # =MIN(105,A1:A44) = 1.90 Row # =MATCH(MIN(105,A1:A44),A1:A44,0) = 8 Any idea why? thanks in advance |
#4
|
|||
|
|||
No I don't know why.
The 105 in the formula is supposed to overlook the hidden cells |
#5
|
||||
|
||||
Isn't he formula supposed to be
=SUBTOTAL(105,A1:A44) and the row =MATCH(SUBTOTAL(105,A1:A44),A1:A44,0)
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#6
|
||||
|
||||
As these are formula solutions I will move the thread to the proper forum
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
|
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA color of visible gridlines of tables word (with some merged cells in first rows) | Alvaro.passi | Word VBA | 0 | 07-18-2017 09:11 AM |
What formula can I use to find the lowest cell in a column that has a value in it? | EddyWD | Excel | 3 | 04-20-2016 09:29 PM |
Find text within cell and return column and row title next to the name on a new sheet. | tanyabowring@live.co.uk | Excel Programming | 2 | 03-26-2015 01:48 AM |
In Excel 2007-After Selecting Visibe Cells-How do I "Copy to Visible cells" Only | mag | Excel | 0 | 10-28-2012 08:04 PM |
text not visible in a number of emails | jbrad | Outlook | 0 | 07-06-2010 03:44 PM |