Array Formula

[Andri]

Dear Experts,

Please find the following formula
=LARGE(IF(AND(AC.TR.NMT="X1234"),AC.STATUS=1),AC.DATE,0),1).

how to make the result (AND(AC.TR,NMT = "X1234"), AC.STATUS-1) as Array
Result?

because if i do "EVALUATE FORMULA" it will result either 0 or 1.
Where we would like the result as Array e,g {0,0,0,1,1,0...) to match the
records of AC.DATE.

thank you for your kind help and guidance.

TIA.

respectfully,
andri

 

[Ron Rosenfeld]

Dear Experts,

Please find the following formula
=LARGE(IF(AND(AC.TR.NMT="X1234"),AC.STATUS=1),AC.DATE,0),1).

how to make the result (AND(AC.TR,NMT = "X1234"), AC.STATUS-1) as Array
Result?
=(AC.TR.NMT="X1234")*(AC.STATUS=1)


because if i do "EVALUATE FORMULA" it will result either 0 or 1.
Where we would like the result as Array e,g {0,0,0,1,1,0...) to match the
records of AC.DATE.

thank you for your kind help and guidance.

TIA.

respectfully,
andri

Your formula is incorrect as written. In particular,
(AND(AC.TR.NMT="X1234"),AC.STATUS=1)

Note that the AND portion: AND(AC.TR.NMT="X1234") will return TRUE if ANY
entry in AC.TR.NMT = X1234.

Also, your opening and closing parentheses do not match.

The combination of those two issues make it difficult to tell exactly what you
want.

If you want to return the LARGEst(k) date corresponding to an entry of X1234 in
AC.TR.NMT that is on the same row as a 1 in AC.STATUS, then try this formula:

This formula must be **array-entered**:

=LARGE((AC.TR.NMT="X1234")*(AC.STATUS=1)*AC.DATE,1)
----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

If you want something else, please be more specific.
--ron

 

[Teethless mama]

=LARGE(IF((Range1=criteria1)*(Range2=criteria2),DateRange))

ctrl+shift+enter, not just enter

 

[T. Valko]

=LARGE(IF(AND(AC.TR.NMT="X1234"),AC.STATUS=1),AC.DATE,0),1).

MAX(....) does the same thing as LARGE(...,1)

Try it like this (array entered**):

=MAX(IF(AC.TR.NMT="X1234",IF(AC.STATUS=1,AC.DATE)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Bernd P]

Hello Andri,

If you need your results for all possible values of AC.TR.NMT you
might want to consider using my UDF Pstat:
http://sulprobil.com/html/pstat.html

Regards,
Bernd