Calculate highest/lowest price from a condition?

[QuantEdge]

Hey excelers,
is it possible to calculate the lowest price within each open position (buy,open,sell) see attached example!
The lowest price should then be taking away from the Buy price.

In financial this is what is refereed to as a open trading position's lowest price (similar to a Max drawdown).

[cbatrody]

Hi QuantEdge,

There is no attachment!

[QuantEdge]

sorry I just attached now

[QuantEdge]

any takers? really need help on this badly!!!!

[Phlegon_of_Tralles]

I'm almost positive that I don't understand what you are truly asking. It's probably above my pay grade.

With that said, for the first set, I put in this formula and got your 'should return' result.
=MIN(A4:A8)-MAX(A4:A8)

Edit: the other two formulas are:

=MIN(A12:A14)-MAX(A12:A14)

=MIN(A20:A30)-MAX(A20:A30)

[MrShorty]

I will go after the low hanging fruit. My first thought, since you have 2016, is to use the MINIFS() function: https://support.office.com/en-us/art...c-72eef32e6599 Are you familiar with this function?

The function itself is syntactically simple. The difficult part is usually the logical part of putting the criteria together correctly. I don't fully understand what you are doing, so I am not sure exactly how to put those criteria together. I would put together any helper columns needed to handle the critieria that I needed (You already have the sell/open/buy column. It looks to me like you might need at least one more column to differentiate between the three sell/open/buy sections). With those columns in place, each desired minimum should be a simple MINIFS() function.

[QuantEdge]

thx Phlegon_of_Tralles, but I need to put your formula in all the cells!

MrShorty, any chance you can create a formula example to show this!
it simple I just need to return the lowest price in a OPEN range of cells... !

[MrShorty]

I can create a formula example, though I thought that the examples in the help file were instructive. Is there something from the help file that you are having trouble understanding?

If you think another example will help, for the case of "find the minimum value in your column A where column B is "OPEN", something like =MINIFS(A1:A31,B1:B31,"OPEN").

[QuantEdge]

sorry shorty that solution don't work? please attachement...

[MrShorty]

My older version does not have MINIFS(), so I cannot see exactly what it is returning. Do you understand how this formula works, so that you understand why it is returning each value it returns? If you don't understand how the MINIFS() function works, then it will be difficult to construct more complex criteria.

As I noted in post #6, I would have expected some additional helper columns to create the more complex criteria for this, but your latest file does not include any additional columns. Assuming I understand your logic, I would have expected:

1) Insert column between B and C.
2) Enter 0 in new C1
3) Formula in C2 like =IF(OR(B2="SELL",B2="BUY"),C1+1,C1) copied down. This should give unique odd integers next to each group of "OPEN"s.
4) Add that column as a criteria range to the MINIFS() function =MINIFS($A$1:$A$31,$B$1:$B$31,"OPEN",$C$1:$C$31,1) would give the minimum where column B is "OPEN" and C is 1, which should correspond to the minimum of the first group of opens. Similar for the remaining groups.

[QuantEdge]

thx MrShorty. I do extract what u say but what do u mean by "4) Add that column..." please see attached sheet?

[MrShorty]

I don't think I understand. You added the $C$1:$C$31 and 1 to the previous MINFS() function just as I suggested. This change should have caused the function to return the 1.17611 which is the minimum for the first set of opens. Is that what you get? If so, then it looks like the function I suggested is working correctly.

You didn't state a new question, but I will anticipate the next question: How do I change the function so that it will return the minimum for the values marked open in column B and 3 in column C? If that is the next question, shouldn't the answer be to put a 3 in for the last argument?

[JohnTopley]

Try (as I don't have Excel 2016)

Using Mr Shorty's helper column in column C (post #10)

in D1

=IF($B1="SELL",MIN(OFFSET($B1,,-1,COUNTIF($C$1:$C$400,$C1)-1)),"")

Copy down

in E1

=IF($B1="SELL",MIN(OFFSET($B1,,-1,COUNTIF($C$1:$C$400,$C1)-1))-INDEX($A$1:$A$400,MATCH($C1+1,$C$1:$C$400,0)),"")

[QuantEdge]

JohnTopley, thx for your help I applied your solution... my shhet fails to upload so here is the screen solution in yellow with AB column returns 0!

could u fix it thx.

[JohnTopley]

Need the file to solve.

[QuantEdge]

try http://wizzlinkz.com/QuantEdge.xlsx

[JohnTopley]

Please post file to this forum.

[QuantEdge]

Hey John
I have attached a new sheet to show the errors in column G and H and show the "should return values" in red.
As simple as I can explain it: I what to return the lowest value in column A, in a range of "OPEN" cells in column D.
or
When column D disappears "OPEN" cell, what is the lowest values in that OPEN range in column A...

[JohnTopley]

In G2

=IF($D2="SELL",MIN(OFFSET($D2,,-3,COUNTIF($F$2:$F$33,$F2)-1)),"")

in H2

=IF($D2="SELL",MIN(OFFSET($D2,,-3,COUNTIF($F$2:$F$33,$F2)-1))-INDEX($A$2:$A$33,MATCH($F2+1,$F$2:$F$33,0)),"")