Array's In Excel

[skay4242]

I am attempting to sort 3 numbers and return the lowest non zero number. The following formula works: {=MIN(IF((D5:D7)>0,(D5:D7)))}

However the location of the numbers D5:D7 are scattered and not next to each other. Is there a way to use arrays with multiple references that are separated? ie {D5,D7,E2}

 

[XL-Dennis]

Aladin,

Did you do that to me by any chance?

Absolutely not!

I was a little bit upset with the smiley when I read Your reply because I thought You would give me (and other as well) a clever formula (like You usually do :wink: when You pointed out the error in the first attempt.

(BTW, I dislike that smiley particular because it does not look very friendly to me)

Nevertheless, I've provided another solution which You may take a look into and comment.

For the OP's part it´s more complex than above - The values is on different sheets and as far as I know arrays can't handle this unless we use UDF.

Kind regards,
Dennis

 

[skay4242]

Nested if is not much quicker than UDF which is much too slow. I had tried that originally. It appears that Excel does not allow non-adjacent array formation. It is an interesting problem all the same ...

 

[skay4242]

Dennis,

Your formula identical to mine at the top (except for defined name ref) returns #Value on my version of excel (97). Perhaps they have fixed this issue in 2000.

Thanks

 

[Aladin Akyurek]

On 2002-09-23 15:22, skay4242 wrote:
Nested if is not much quicker than UDF which is much too slow. I had tried that originally. It appears that Excel does not allow non-adjacent array formation. It is an interesting problem all the same ...

Anything yet on method B? Don't worry about EVAL, etc. They are as fast as a built-in functions.

 

[skay4242]

B is the best solution I am sure. However, as with others I am on an office build that has no admin rights for users. Using an add-in would require due diligence by the powers that be. Also need to apply to other machines. Hence I am hesitant to try to go down that path.

 

[XL-Dennis]

skay4242,

Funny, I´m using swedish versions of 97 SR-2B, 2000 SR-1 and XP SP-2 and it runs as expected.

Is it possible for You to distribute the Add-in Morefunc with Your workbook?

Kind regards,
Dennis

 

[Aladin Akyurek]

[...] I thought You would give me (and other as well) a clever formula [...]
[...]

Dennis,

Since we're talking about the 3 nonconsecutive cells, how about...array-entered

=IF(STDEV(Array)=0,"",IF(SMALL(Array,1)<>0,SMALL(Array,1),IF(SMALL(Array,2)<>0,SMALL(Array,2),IF((SMALL(Array,3)<>0),SMALL(Array,3)))))

where Array refers to those 3 cells.

Aladin

 

[XL-Dennis]

Aladin,

Nice very nice, especially the STDEV-part

Kind regards,
Dennis