Help on finding MAX and MIN with conditions

Hello friends,

I am working on finding performance on a sample data... I am facing a hurdle with finding the MAX and MIN with conditions. Will appreciate help on the same...

I am attaching the file for better understanding..... The problem cells have "?" in red color in them...

Agree that some sample answers will help us.

Originally Posted by kersplash

What numbers are you expecting to be where you have the red question marks? ie. how do you calculate these figures.

I am looking at calculating the maximum PROFIT & LOSS for closed (Target achieved, Stop Loss Hit, Expired) and for ACTIVE trades...

I am reattaching the file with comments written in the cells to make it simpler and easier to understand....

You still did not give any sample answers?
(and why bother putting what you want, in as a comment, you could just have told us here)

Try thios to start with for AB23
=MAX(IF($J$2:$J$401<>"Active",$K$2:$K$401))

Originally Posted by FDibbins

You still did not give any sample answers?
(and why bother putting what you want, in as a comment, you could just have told us here)

Try thios to start with for AB23
=MAX(IF($J$2:$J$401<>"Active",$K$2:$K$401))

Explaining here would not serve the purpose as it would not be clear what I am trying to say... hence, put it as comments explaining what i am trying to get and what columns need to be referred to....

What formula do I need in the other cells?

Here are some possibilities for the other formulas*.
For AB24: =MIN(IF($J$2:$J$401<>"Active",$K$2:$K$401))
For AC23 (drag across to AD23 AFTER activating): =MAX(IF($J2:$J401<>"Active",IF($C2:$C401=AC1,$K2:$K401)))
For AC24 (drag across to AD24 AFTER activating): =MIN(IF($J2:$J401<>"Active",IF($C2:$C401=AC1,$K2:$K401)))
For AB31: =MAX(IF($J$2:$J$401<>"Active",$M$2:$M$401))
For AB32: =MIN(IF($J$2:$J$401<>"Active",$M$2:$M$401))
For AC31 (drag across to AD31 AFTER activating): =MAX(IF($J2:$J401<>"Active",IF($C2:$C401=AC1,$M2:$M401)))
For AC32 (drag across to AD32 AFTER activating): =MIN(IF($J2:$J401<>"Active",IF($C2:$C401=AC1,$M2:$M401)))
*All of these are array entered formulas which means that after selecting the cell and pasting the formula into the formula bar, the Ctrl, Shift and Enter keys need to be pressed simultaneously. In the case of the cells in column AC this should be done BEFORE they are copied to column AD.
The formulas for the "Active" profit and loss cells will be similar, however change the <> symbol to =
Let us know if you have any questions.