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.

 

[mike2246]

Got an error when applying newest update. and i'm not sure if it's manual or filter. but I have VBA Code setup so that if a cell = No to hide rows for that player and Yes to be visable for all sheets

 

[jasonb75]

Got an error when applying newest update.

Try this one instead

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

 

[mike2246]

Perfect, Thanks!!! if it's not to much so I can learn more what do all the #'s mean? 15,6,1? etc?

and is there an easy way to switch this to Max value instead of Min?

 

[mike2246]

or even for Average, I like using this method over VBA

 

[jasonb75]

is there an easy way to switch this to Max value instead of Min?

Just change 15 to 14, don't think it can get much easier than that.

For average, I think that it should work with this formula, array confirmed with Ctrl Shift Enter

=AVERAGE(IFERROR(1/(1/SUBTOTAL(109,OFFSET(A2,ROW(A2:A99)-ROW(A2),0,1,1))),""))

15 (SMALL), 6 (Ignore errors) and 104 (MAX) are function arguments, they are all listed in the tooltips which will show up if you edit the formula and delete the current number.
The last one, 104 would work with any one of several options used. 101,104,105,106 or 109 should all work for this formula.

1/(1/ is an error trap to eliminate zero values.

0,1,1))) sets an offset position from A2, the ROW() part creates an offset to each individual row from A2:A99, 0,1,1 sets no column offset, i.e. use the same column, 1 row tall, 1 column wide.
The final 1 at the end of the formula combined with the 15 at the start tells the aggregate function that you want the smallest value from the valid results, changing it to 2 would give the second smallest number (in the event of a tie with the smallest number, the same result would still be shown).

 

[mike2246]

Thanks for explanation, max worked perfectly. Average didn't work I get #Value!

 

[mike2246]

I got it, searched the Aggregate Function and used the following for Average
=AGGREGATE(1,7,A2:A99)

 

[mike2246]

Just realized that formula works, but doesn't ignore 0

 

[jasonb75]

This was the problem that we had with the first formula that I posted, aggregate appears to work with hidden rows or arrays (an array was used to ignore 0) but it doesn't work with both in the same formula.

Average didn't work I get #Value!

If you got that then it means that the array is not confirmed correctly.

Copy the formula from my earlier reply and past it into the cell, hold down Ctrl and Shift, then press Enter.