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

 

[ANAND KUMAR]

True Aladin...........According to post #4 we need to look each cell one by one. But would it not be appropriate to make this process shorter with Name Range for scattered cells?

 

[Andrew Fergus]

An interesting thought Anand. I tested this and if I declare a non-contiguous range as a Named Range then I can use that in a simple formula like MIN, but as soon as I use CSE I get an error......

 

[Aladin Akyurek]

True Aladin...........According to post #4 we need to look each cell one by one. But would it not be appropriate to make this process shorter with Name Range for scattered cells?

The question is: What is the lowest (smallest) greater than zero value among the values, say, a1, f7, j2, and K6 house?

 

[ANAND KUMAR]

An interesting thought Anand. I tested this and if I declare a non-contiguous range as a Named Range then I can use that in a simple formula like MIN, but as soon as I use CSE I get an error......

An error was not supposed to appear there. I have retested without CSE and met #VALUE while got desired output with CSE. (As we can refer attached Image in Post 9).
Also tried dual ways to create Name Range, a) Selected entire portion by mouse from my starting point to last position of my Data and Then Simply captured this area under Name Box (Just Left to Formula Bar).
b) From Ribbon Bar Under Formulas>Name Manger>New, filled out all the relevant fields and finally Ok.
And the result using these methods is same to fetch the output.

 

[ANAND KUMAR]

The question is: What is the lowest (smallest) greater than zero value among the values, say, a1, f7, j2, and K6 house?

Agreed.........Probably I'm missing something among of these listed out,
1) Considering that we need to search next biggest number after Zero among the values- A1, F7, J2, K6
2) The mentioned formula should work IF I use a Range A1:K7 instead writing every reference for every time.
3) OR Stepping next, I can make this process much easier if I quote this Range A1:K7 by defining a Name Range, So that I can get rid from giving selection.

I'm in favor to follow last two steps to achieve first one within less amount of effort. I would like to welcome your words about my missing point.

 

[Aladin Akyurek]

Agreed.........Probably I'm missing something among of these listed out,
1) Considering that we need to search next biggest number after Zero among the values- A1, F7, J2, K6
2) The mentioned formula should work IF I use a Range A1:K7 instead writing every reference for every time.
3) OR Stepping next, I can make this process much easier if I quote this Range A1:K7 by defining a Name Range, So that I can get rid from giving selection.

I'm in favor to follow last two steps to achieve first one within less amount of effort. I would like to welcome your words about my missing point.

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

40​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

42​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

30​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The question that the OP has in mind is the following:

What is the lowest value greater than zero given the set of values in A1, F7, J2, and K6? The answer is 30.

 

[ANAND KUMAR]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]

40​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

42​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

15​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

30​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

0​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The question that the OP has in mind is the following:

What is the lowest value greater than zero given the set of values in A1, F7, J2, and K6? The answer is 30.

It's true that sample of data helps a lot to understand requirement. I thought that data is kept within a combined range.

Thanks a bunch Aladin for making picture more visible. Hope you didn't mind anything so far.

@ dsmith1088 - If your sample data looks like this then Apologies for not getting your point in first go.

So you may go with Aladin's solution if cells are scattered in this way.

 

[Andrew Fergus]

An error was not supposed to appear there. I have retested without CSE and met #VALUE while got desired output with CSE. (As we can refer attached Image in Post 9).
Also tried dual ways to create Name Range, a) Selected entire portion by mouse from my starting point to last position of my Data and Then Simply captured this area under Name Box (Just Left to Formula Bar).
b) From Ribbon Bar Under Formulas>Name Manger>New, filled out all the relevant fields and finally Ok.
And the result using these methods is same to fetch the output.

Hello Anand
I saw that image from earlier thanks, that's why I tried a named range with non-contiguous cells i.e. just t A1, F7, J2, K6 in the named range. CSE formula gives an error.
Regards
Andrew