A complex formula, help needed

[Mac]

Hello all,
please help me with a complex formula to assign to a cell; pseudocode for it
goes like: for (i = 1 to 6 ; i++){ if (( Ai == value1) AND (Di == value1))
then (for( j = 7 to 25){ if (Bj == value2) then (write XX into this cell)}
)}; although it is an obvious candidate for vba, I wonder if the procedure
can be realized via cell formulae...

 

[Lars-Åke Aspelin]

Hello all,
please help me with a complex formula to assign to a cell; pseudocode for it
goes like: for (i = 1 to 6 ; i++){ if (( Ai == value1) AND (Di == value1))
then (for( j = 7 to 25){ if (Bj == value2) then (write XX into this cell)}
)}; although it is an obvious candidate for vba, I wonder if the procedure
can be realized via cell formulae...

Don't know if I understood you pseudocode correctly, but try this

=IF(AND(AND(A1:A6=1,D16=value1),AND(B7:B25=value2)),XX)

Note: This is an array formula that has to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

Replace value1, value2 and XX with the values you want to use.

The result of the formula is XX if all of A1:A6 ans D16 have the
value1 at the same time as all of B7:B25 has the value2.
Other wise the result is FALSE.

Hope this helps. / Lars-Åke

 

[Rick Rothstein]

Like Lars-Ã…ke, I'm not sure I under exactly what you want either, but here
is my attempt. Put this normally entered formula in your first cell (in Row
7 somewhere I'm guessing) and copy it down...

=IF(AND(COUNTIF(A$1:A$6,Value1)=COUNTIF(B$1:B$6,Value1),B7=Value2),"XX","")

Replace the two Value1 references with your actual first value and the
single Value2 reference with your second value (I've assumed "XX" is what
you wanted to display in the cell if the conditions are met).