Smallest value greater than zero across multiple cells

[dsmith1088]

Hi all

I'm trying to sum the smallest value greater than zero across multiple (scattered/random) cells. I know how to do this if the cells were in straightforward row or column range, but not sure what the best way is across random cells. Any ideas?

Many thanks

 

[Aladin Akyurek]

Which cells? Would you name some of these?

 

[dsmith1088]

For example:

A1 = 3,000
A26 = 2,000
A38 = 0
A50 = 1,500


I need a formula that looks across each of those 4 cells and returns the lowest value greater than zero i.e. A50 (1,500).

 

[Aladin Akyurek]

For example:

A1 = 3,000
A26 = 2,000
A38 = 0
A50 = 1,500


I need a formula that looks across each of those 4 cells and returns the lowest value greater than zero i.e. A50 (1,500).

Control+shift+enter, not just enter:

=MIN(IF(CHOOSE(ROW(INDIRECT("1:"&COUNT(A1,A26,A38,A50))),A1,A26,A38,A50)>0,CHOOSE(ROW(INDIRECT("1:"&COUNT(A1,A26,A38,A50))),A1,A26,A38,A50)))

 

[dsmith1088]

Oh! I thought it was going to be simpler than that, but thank you. That has worked. I didn't think I would need to reference the cells so many times.

Thank you for your help

 

[Aladin Akyurek]

Oh! I thought it was going to be simpler than that, but thank you. That has worked. I didn't think I would need to reference the cells so many times.

Thank you for your help

You are welcome.

You have a point though. If MS would include a native function set like SETV/GETV Longre has designed, we would have:

MIN(IF(SETV(CHOOSE(ROW(INDIRECT("1:"&COUNT(A1,A26,A38,A50))),A1,A26,A38,A50))>0,GETV()))

Adding a native version of his INTVECTOR would make life even easier.

But, alas.

 

[ANAND KUMAR]

See if this works,

=MIN(IF(A1:A50>0,A1:A50))

Please note that array processing, So use with ctrl+shift+enter.

 

[dsmith1088]

Hi

Thanks for your reply. Yes that would work across a continuous array of cells, but my cells are scattered i.e they are not adjacent to each other.

 

[ANAND KUMAR]

Hi

Thanks for your reply. Yes that would work across a continuous array of cells, but my cells are scattered i.e they are not adjacent to each other.

A quick way is possible to deal with scattered cells. Define a Name Range and pass this name through formula.

Say I have created a Name Range from $A$1:$L$15 and nominate this reference with MYRange.

Then formula becomes;-

=MIN(IF(MyRange>0,MyRange)) with CSE.

See Sample - https://ibb.co/fxhtD7

 

[Aladin Akyurek]

@ ANAND KUMAR

See post #4 .