A complex formula, help needed

M

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...
 
L

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,D1:D6=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 D1:D6 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
 
R

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).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top