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