Find Min Value in Range Excluding 0 and Hidden Cells

[mike2246]

Cell A100 Find Min of A2-A99 excluding 0's and Hidden rows. I found a website that shows how to do this for average by making a VB Module and it works perfect, just not sure how to change this to a Min Value.

Website Reference for Avg VB Code

Averaging without Hidden Cells

Grabbing an average of a range of cells is easy using Excel functions. If you want that average to ignore hidden cells when the range consists of a row, then Excel doesn't have a way to get what you need. This tip examines a couple of ways you can get around Excel's deficiency.

excelribbon.tips.net

Or this could also work in my situation if it could be done through conditional formatting, doing a gradient scale of lowest value that ignore's 0's and hidden cells.

 

[jasonb75]

Do you need code?

With a formula

=AGGREGATE(15,7,1/(1/A2:A99),1)

 

[mike2246]

Do you need code?

With a formula

=AGGREGATE(15,7,1/(1/A2:A99),1)

That works for excluding 0's but I also need to exclude hidden rows

 

[jasonb75]

Using option 7 as the second argument of AGGREGATE does ignore hidden rows.

edit:-

Correction, option 7 is supposed to ignore hidden rows, it doesn't appear to work as it is meant to. I'll be back shortly with a new formula.

 

[Fluff]

Unfortunately I don't think it ignores hidden rows, when using an array.

 

[jasonb75]

Yeah, that's correct, Fluff. This works though

=AGGREGATE(15,6,1/(1/SUBTOTAL(4,OFFSET(A2,ROW(A2:A99)-1,0,1,1))),1)

 

[Eric W]

Yeah, that's correct, Fluff. This works though

=AGGREGATE(15,6,1/(1/SUBTOTAL(4,OFFSET(A2,ROW(A2:A99)-1,0,1,1))),1)

I'd suggest:

=AGGREGATE(15,6,1/(1/SUBTOTAL(4,OFFSET(A2,ROW(A2:A99)-ROW(A2),0,1,1))),1)

otherwise it would ignore A2.

 

[jasonb75]

Good catch Eric, I started at A1 then changed it and forgot to correct the adjustment.

 

[mike2246]

Still not working using updated formula, Value of A2 = 2, It still shows minimum as 2 even if row is hidden

 

[Eric W]

If you're using an actual filter, the formula should work ok. But if you are manually hiding a row, try this version:

=AGGREGATE(15,6,1/(1/SUBTOTAL(104,OFFSET(A2,ROW(A2:A99)-1,0,1,1))),1)