for visible cells (filtered), how find value lowest than 4.00 and return the cell number

**murfy69** · 08-02-2017, 05:26 PM

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

**quekbc** · 08-02-2017, 08:21 PM

The only method that i know how is to first use a helper column.

Assuming that values you have is A1:A8

_Row\^Col	A	B	C
1	10	10	=IF(A1 < $B$10, "n/a",A1)
2	9.22	9.22	copy down from above
3	7.33	7.33
4	5.44	5.44
5	4.67	4.67
6	3.12	n/a
7	2.1	n/a
8	1.9	n/a
9
10	Limit	4	Input
11	Minimum	4.67	=SUBTOTAL(105, B1:B8)
12	Row	5	=ROW(INDEX(B1:B8, MATCH(B11, B1:B8, 0)))

**FlameRetired** · 08-02-2017, 09:54 PM

1- how can i make this formula work ONLY for unhidden cells?
2- how can i return the line/column of that cell?

I interpreted this a little differently as cell address.

In B11 and B12:
Formulas are (same as quekbc's)

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

Unfiltered.

Row\Col	A	B
1	amts
2	10
3	9.22
4	7.33
5	5.44
6	4.67
7	3.12
8	2.1
9	1.9
10
11		A9
12		1.9

Filtered >4

Row\Col	A	B
1	amts
2	10
3	9.22
4	7.33
5	5.44
6	4.67
10
11		A6
12		4.67

**murfy69** · 08-02-2017, 10:36 PM

It worked with your suggestion.
Thanks a lot

**FlameRetired** · 08-02-2017, 10:45 PM

Good deal.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

**Pepe Le Mokko** · 08-03-2017, 06:19 AM

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

for visible cells (filtered), how find value lowest than 4.00 and return the cell number

LinkBack

Thread Tools

Rate This Thread

Display

for visible cells (filtered), how find value lowest than 4.00 and return the cell number

Re: for visible cells (filtered), how find value lowest than 4.00 and return the cell numb

Re: for visible cells (filtered), how find value lowest than 4.00 and return the cell numb

Re: for visible cells (filtered), how find value lowest than 4.00 and return the cell numb

Re: for visible cells (filtered), how find value lowest than 4.00 and return the cell numb

Re: for visible cells (filtered), how find value lowest than 4.00 and return the cell numb

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Fill number series down a filtered range in visible cells only

Find lowest value from 4 numbers in various cells and return text

[SOLVED] Need help - Match number in column, find lowest number in that row, and return header name

How do I code to return the lowest 2 number is a list of cells

[SOLVED] Find lowest value in corresponding cells on multiple worksheets. Return value & location.

Find lowest value in corresponding cells on multiple worksheets. Return value & location.

Row number of first visible cell in a filtered range

Bookmarks

Bookmarks

Posting Permissions