Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-02-2017, 02:26 PM
murfy69 murfy69 is offline for visible cells (filtered), how find value lowest than 4.00 and return the cell number Windows 7 64bit for visible cells (filtered), how find value lowest than 4.00 and return the cell number Office 2016
Novice
for visible cells (filtered), how find value lowest than 4.00 and return the cell number
 
Join Date: Aug 2017
Posts: 2
murfy69 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 08-02-2017, 03:17 PM
Logit Logit is offline for visible cells (filtered), how find value lowest than 4.00 and return the cell number Windows 10 for visible cells (filtered), how find value lowest than 4.00 and return the cell number Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

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)
Reply With Quote
  #3  
Old 08-02-2017, 06:33 PM
murfy69 murfy69 is offline for visible cells (filtered), how find value lowest than 4.00 and return the cell number Windows 7 64bit for visible cells (filtered), how find value lowest than 4.00 and return the cell number Office 2016
Novice
for visible cells (filtered), how find value lowest than 4.00 and return the cell number
 
Join Date: Aug 2017
Posts: 2
murfy69 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 08-02-2017, 08:30 PM
Logit Logit is offline for visible cells (filtered), how find value lowest than 4.00 and return the cell number Windows 10 for visible cells (filtered), how find value lowest than 4.00 and return the cell number Office 2007
Expert
 
Join Date: Jan 2017
Posts: 529
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

No I don't know why.

The 105 in the formula is supposed to overlook the hidden cells
Attached Files
File Type: xlsx MIN and Row.xlsx (9.7 KB, 7 views)
Reply With Quote
  #5  
Old 08-02-2017, 11:37 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline for visible cells (filtered), how find value lowest than 4.00 and return the cell number Windows 7 64bit for visible cells (filtered), how find value lowest than 4.00 and return the cell number Office 2010 64bit
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

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
Reply With Quote
  #6  
Old 08-02-2017, 11:38 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline for visible cells (filtered), how find value lowest than 4.00 and return the cell number Windows 7 64bit for visible cells (filtered), how find value lowest than 4.00 and return the cell number Office 2010 64bit
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

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
Reply With Quote
  #7  
Old 08-03-2017, 12:03 AM
xor xor is offline for visible cells (filtered), how find value lowest than 4.00 and return the cell number Windows 10 for visible cells (filtered), how find value lowest than 4.00 and return the cell number Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,097
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

https://www.excelforum.com/excel-for...ll-number.html
Reply With Quote
Reply

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
for visible cells (filtered), how find value lowest than 4.00 and return the cell number 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

Other Forums: Access Forums

All times are GMT -7. The time now is 03:25 PM.


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