# Calculate highest/lowest price from a condition?

1. ## Calculate highest/lowest price from a condition?

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).  Register To Reply

2. ## Re: Calculate highest/lowest price from a condition?

Hi QuantEdge,

There is no attachment!  Register To Reply

3. ## Re: Calculate highest/lowest price from a condition?

sorry I just attached now  Register To Reply

4. ## Re: Calculate highest/lowest price from a condition?

any takers? really need help on this badly!!!!  Register To Reply

5. ## Re: Calculate highest/lowest price from a condition?

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)  Register To Reply

6. ## Re: Calculate highest/lowest price from a condition?

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.  Register To Reply

7. ## Re: Calculate highest/lowest price from a condition?

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... !  Register To Reply

8. ## Re: Calculate highest/lowest price from a condition?

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").  Register To Reply

9. ## Re: Calculate highest/lowest price from a condition?

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

10. ## Re: Calculate highest/lowest price from a condition?

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.  Register To Reply

11. ## Re: Calculate highest/lowest price from a condition?

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

12. ## Re: Calculate highest/lowest price from a condition?

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?  Register To Reply

13. ## Re: Calculate highest/lowest price from a condition?

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)),"")  Register To Reply

14. ## Re: Calculate highest/lowest price from a condition?

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.  Register To Reply

15. ## Re: Calculate highest/lowest price from a condition?

Need the file to solve.  Register To Reply

16. ## Re: Calculate highest/lowest price from a condition?  Register To Reply

17. ## Re: Calculate highest/lowest price from a condition?

Please post file to this forum.  Register To Reply

18. ## Re: Calculate highest/lowest price from a condition?

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...  Register To Reply

19. ## Re: Calculate highest/lowest price from a condition?

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)),"")  Register To Reply