conditional minimum formula

[Derrick]

im looking for a formula where a minimum will be returned, if the cells
aren't 0 or blank.
for example:
Spans# 1 - 120", #2 - 80", #3 - 0"
Anti-buckling clip spacing = 24"

so, we have 2 spans, with anti-buckling clips at 24" on centre.
I would like the cell containing the formula to equal 24.

Lets now say we don't have any anti-buckling clips, and the cell is left
blank.
Spans# 1 - 120", #2 - 80", #3 - 0"
Anti-buckling clip spacing =

The cell should read: 80

Any ideas? im trying to work with a nested if statement inside a =min()
function, but im not sure how to return a 'nothing here, move on' line.
Thanks!

 

[Bernard Liengme]

Please tell (or show) us what is in a few sample cells - also tell us what
cells are being used for these. All the references to spans and spacing is
meaningless to us.

=MIN(IF(A1:A10<>0,A1:A10)) will return the non-zero minimum of the range
A1:A10
It is an array formula so must be committed with CTRL+SHIFT+ENTER not just
ENTER

best wishes

 

[Jacob Skaria]

With spans in cells A1:A3 and Anti-buckling clip spacing in B1; try the below
formula

=SMALL((A1:A3,B1),COUNTIF(A1:A3,0)+1)

If this post helps click Yes

 

[Derrick]

ok sorry. i was hoping spans and stuff were helping . .the column titles etc.

A2:A4 (spans) = 120",80",0" respectively
B2 (clip spacing) = 24"

i want C2 (Min length) = 24.

if B2 = "" (Left blank because of no clips)

i want C2= 80

Also, im not a fan of using arrays... i can, but i have problems getting
them to work

does this help you?

 

[Jacob Skaria]

=SMALL((A1:A4,B2),COUNTIF(A1:A4,0)+1)

If this post helps click Yes

 

[Derrick]

can you explain how this formula works?
Small(Array,K)
countif(range, criteria)
so minimum non-zero value = Small(Array,K =countif(Range, criteria))

1. I get the (A1:A3,B1).
2. doesn't countif return the number of cells which meet the criteria? so
if A1:A3 cells are non-0, it returns 3, then plus 1 - so 4
3. then, small looks for the 4th smallest - the largest one?
4. why not include B1 in the countif?

am i right or am i making a mistake in my logic?

 

[Derrick]

jacob, this formula will does not include B2.
I put it in, and if it's smaller, it still isn't being used. i tried
=SMALL((A1:A4,B2),COUNTIF((A1:A4,B2),0)+1)
and that returns a =#Value error

 

[Bernard Liengme]

Sorry be be a dolt, but I still cannot see what column A has to do with the
problem

=IF(B2="",80,MIN(B22))
will give the minimum value of B22 if B2 is not blank
Does this help?
best wishes