MIN Formula question

[jasono]

Does anybody know how to use the MIN formula and EXCLUDE the value of 0?

I have a column that when a user enters a value, a zero can be the end result. I want it to display the lowest value other than 0.

Thank you,
Jason

 

[Aladin Akyurek]

On 2002-04-19 12:26, jasono wrote:
Does anybody know how to use the MIN formula and EXCLUDE the value of 0?

I have a column that when a user enters a value, a zero can be the end result. I want it to display the lowest value other than 0.

Thank you,
Jason

Ok. I enter 0 in A10 as user. Where do I get the lowest value other than what I entered?

Care to clarify?

 

[jasono]

I am doing a spreadsheet on Euclids Formula (using Mod's).

I have to have enough cells filled with the formula to compute several different circumstances (the GCD might only have 4 divisons on it or it could have 10).

I would like to take the GCD and display the answer. The problem comes from I don't know which cell will have the final answer in it.

Sound confusing? I think it does

 

[jasono]

Number 1 Number 2
Enter your numbers >>>>>> 77777 889

GCD>>> 77777 889
889 434
434 21
21 14
14 7
7 0
0 0
0 0
0 0
0 0
0 0
0 0 is>>>>

Here is what the spread sheet looks like. In this case I would need to return a value of 7. In other cases the final number might be in any column on the left. MIN returns the lowest value which is 0.
This message was edited by jasono on 2002-04-19 12:52

 

[Aladin Akyurek]

On 2002-04-19 12:51, jasono wrote:

Number 1 Number 2
Enter your numbers >>>>>> 77777 889

GCD>>> 77777 889
889 434
434 21
21 14
14 7
7 0
0 0
0 0
0 0
0 0
0 0
0 0 is>>>>

Here is what the spread sheet looks like. In this case I would need to return a value of 7. In other cases the final number might be in any column on the left. MIN returns the lowest value which is 0.
This message was edited by jasono on 2002-04-19 12:52

Array-enter:

=MIN(IF(Range-of-GCD-numbers,Range-of-GCD-numbers))

Insert the true range in the above formula.

In order to array-enter a formula, hit control+shift+enter at the same time, not just enter.

Aladin

 

[Tim Francis-Wright]

On 2002-04-19 12:51, jasono wrote:

Number 1 Number 2
Enter your numbers >>>>>> 77777 889

GCD>>> 77777 889
889 434
434 21
21 14
14 7
7 0
0 0
0 0
0 0
0 0
0 0
0 0 is>>>>

Here is what the spread sheet looks like. In this case I would need to return a value of 7. In other cases the final number might be in any column on the left. MIN returns the lowest value which is 0.
This message was edited by jasono on 2002-04-19 12:52

One way is to use an array formula:
if A4:A13 have your left-column figures,
use
=MIN(IF(A4:A13>0,A4:A13))
[you need to enter this with Control-Shift-Enter instead of just Enter]

Another way is to modify what you have in column B:
B5 right now has = MOD(A4,B4)
use this instead = IF(MOD(A4,B4)=0,B4,MOD(A4,B4))
...and then use that in each row. It keeps the right-hand column from hitting 0 in the first place.

HTH