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}

 

[Aladin Akyurek]

Two options...

A) Apply the aary formula to a range of consecutive cells to which you can link the non-consecutive cells;

(B) array-enter:

=MIN(IF(SETV(EVAL(CHAR(123)&D5&","&D7&","&E2&CHAR(125))),GETV()))

SETV, GETV, and EVAL are part of the morefunc.xll add-in, downloadable from:

http://longre.free.fr/english/index.html
This message was edited by Aladin Akyurek on 2002-09-23 11:16

 

[skay4242]

Thanks for the assistance. As it turns out I went back to the original 3 cells and altered the formulas to give only one result. The first solution also does not work due to the fact that i really have three matrices of data with which I need to compare. Putting each sell next to each other slows down the processing. I am running 10000 monte carlo simulations around the data sets so speed is crucial. Thanks again.

 

[Aladin Akyurek]

On 2002-09-23 11:34, skay4242 wrote:
Thanks for the assistance. As it turns out I went back to the original 3 cells and altered the formulas to give only one result. The first solution also does not work due to the fact that i really have three matrices of data with which I need to compare. Putting each sell next to each other slows down the processing. I am running 10000 monte carlo simulations around the data sets so speed is crucial. Thanks again.

Try B to see whether that works.

 

[XL-Dennis]

Hi,

Actually another options (at least it worked for me ) is available:

=IF(MIN(Arrays)=0,SMALL(Arrays,2),MIN(Arrays))

Where the name Array refer to the range.

This is an array-formula which means that it´s entered with Ctrl´+SHift+Enter and XL will confirm by using brackets {} around the formula,

{=IF(MIN(Arrays)=0,SMALL(Arrays,2),MIN(Arrays))}

Kind regards,
Dennis

 

[Aladin Akyurek]

On 2002-09-23 14:14, XL-Dennis wrote:
Hi,

Actually another options (at least it worked for me ) is available:

=IF(MIN(Arrays)=0,SMALL(Arrays,2),MIN(Arrays))

Where the name Array refer to the range.

This is an array-formula which means that it´s entered with Ctrl´+SHift+Enter and XL will confirm by using brackets {} around the formula,

{=IF(MIN(Arrays)=0,SMALL(Arrays,2),MIN(Arrays))}

Kind regards,
Dennis

Try when Arrays refer to the nonconsec range with values...

D5=0
D7=3
E2=0

 

[XL-Dennis]

Aladin,

You may laugh but back to the OP´s question (only 3 cells....) and in view of Your finding we can expand the formula to cover all possibilities with nested IF-statements, right?

See below answer from me

Kind regards,
Dennis
This message was edited by XL-Dennis on 2002-09-23 15:15

 

[Dave Patton]

The formula was not correct.
This message was edited by Dave Patton on 2002-09-23 15:36

 

[XL-Dennis]

Dave,

I can´t get Your solution to work?

Nevertheless, Aladin take a look on this solution and see if You beat this "horrible" formula :wink:

Diverse1.xls
	A	B	C	D	E	F	G	H
1
2			0		0
3
4					1
5
6			1
7
8			Array	=Blad1!$E$2;Blad1!$C$2;Blad1!$E$4
9
10
Blad1

Kind regards,
Dennis

 

[Aladin Akyurek]

[...] You may laugh

I've been punished... My Netscape has now that grinning face as background I can't get rid off. Did you do that to me by any chance?

but back to the OP´s question (only 3 cells....) and in view of Your finding we can expand the formula to cover all possibilities with nested IF-statements, right?

My question is to You:


Is it possible or not?

Yeah, right. If it's just a few cells, it's no doubt a viable option. I have no idea though what the performance will be as part of the solver model the OP appears to use.

Aladin