Min If greater than zero

[dsmith1088]

Hi all

I have a number of scattered cells which are NOT in a list or a table e.g. A1, C5, E10, F69.

i need to select the lowest value greater than zero without using a long-winded nested if formula. Any ideas? I know this is very straightforward if the cells are in a list or a table because I'll have a straight forward range to work with (e.g. A1:A10), but unfortunately there is no way to edit the spreadsheet in this manner.

Many thanks

 

[oldbrewer]

A1=4 B3=5 C5=6

highlight all 3 cells with control + select

insert name, define, myrange

=sum(myrange)=15

 

[oldbrewer]

then use small function to list all cells in a helper column and select the first one above zero - helper can be hidden if desired.

 

[admiral100]

Another option

Ctrl+Shift+Enter

=SMALL((A1,H6:H8,J9,M4:M8,I1),1+FREQUENCY((A1,H6:H8,J9,M4:M8,I1),0))


Credit to pgc01

 

[dsmith1088]

Thanks for the quick response. I've got the myrange working but not too sure what you mean about the "small function" and "helper column". What I really need is a formula that will return the minimum value greater than zero - i then need to incorporate this formula into a larger formula.

For example: if(MinZeroFormula>R10,"Ok","Cancel")

 

[oldbrewer]

a1=9
b1=4
c1=22

=small(A1:C1,2) returns the second smallest eg 9

a helper column is somewhere you place intermediate workings, and can be located out of sight - excel does have a few spare columns....