Microsoft Office Forums for visible cells (filtered), how find value lowest than 4.00 and return the cell number
 User Name Remember Me? Password
 Register FAQ Search Today's Posts Mark Forums Read

 Thread Tools Display Modes
#1
08-02-2017, 02:26 PM
 murfy69 Windows 7 64bit Office 2016 Novice Join Date: Aug 2017 Posts: 2
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
08-02-2017, 03:17 PM
 Logit Windows 10 Office 2007 Expert Join Date: Jan 2017 Posts: 445

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
08-02-2017, 06:33 PM
 murfy69 Windows 7 64bit Office 2016 Novice Join Date: Aug 2017 Posts: 2

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
08-02-2017, 08:30 PM
 Logit Windows 10 Office 2007 Expert Join Date: Jan 2017 Posts: 445

No I don't know why.

The 105 in the formula is supposed to overlook the hidden cells
Attached Files
 MIN and Row.xlsx (9.7 KB, 7 views)
#5
08-02-2017, 11:37 PM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,586

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
08-02-2017, 11:38 PM
 Pecoflyer Windows 7 64bit Office 2010 64bit Expert Join Date: Nov 2011 Location: Brussels Belgium Posts: 2,586

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
08-03-2017, 12:03 AM
 xor Windows 10 Office 2016 Expert Join Date: Oct 2015 Posts: 1,080

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post Alvaro.passi Word VBA 0 07-18-2017 09:11 AM EddyWD Excel 3 04-20-2016 09:29 PM tanyabowring@live.co.uk Excel Programming 2 03-26-2015 01:48 AM mag Excel 0 10-28-2012 08:04 PM jbrad Outlook 0 07-06-2010 03:44 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 10:23 PM.

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

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