+ Reply to Thread
Results 1 to 19 of 19

Calculate highest/lowest price from a condition?

  1. #1
    Forum Contributor
    Join Date
    08-18-2017
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    308

    Talking 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).
    Attached Files Attached Files
    Last edited by QuantEdge; 09-14-2017 at 07:30 AM.

  2. #2
    Forum Expert cbatrody's Avatar
    Join Date
    04-15-2014
    Location
    Dubai
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    2,134

    Re: Calculate highest/lowest price from a condition?

    Hi QuantEdge,

    There is no attachment!

  3. #3
    Forum Contributor
    Join Date
    08-18-2017
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Calculate highest/lowest price from a condition?

    sorry I just attached now
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-18-2017
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Calculate highest/lowest price from a condition?

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

  5. #5
    Forum Contributor
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    2010
    Posts
    114

    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)

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,886

    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.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Forum Contributor
    Join Date
    08-18-2017
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    308

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

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,886

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

  9. #9
    Forum Contributor
    Join Date
    08-18-2017
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Calculate highest/lowest price from a condition?

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

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,886

    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.

  11. #11
    Forum Contributor
    Join Date
    08-18-2017
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    308

    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?
    Attached Files Attached Files

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,886

    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?

  13. #13
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    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)),"")
    Attached Files Attached Files

  14. #14
    Forum Contributor
    Join Date
    08-18-2017
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    308

    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.
    Attached Images Attached Images

  15. #15
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Calculate highest/lowest price from a condition?

    Need the file to solve.

  16. #16
    Forum Contributor
    Join Date
    08-18-2017
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    308

    Re: Calculate highest/lowest price from a condition?


  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    Re: Calculate highest/lowest price from a condition?

    Please post file to this forum.

  18. #18
    Forum Contributor
    Join Date
    08-18-2017
    Location
    London
    MS-Off Ver
    Excel 2016
    Posts
    308

    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...
    Attached Files Attached Files

  19. #19
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    16,518

    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)),"")
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Comparing my price to the lowest competitor's price in an excel row
    By yr25 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-10-2017, 11:05 AM
  2. [SOLVED] how to find the lowest to highest value and display the name who is the lowest and the hig
    By jasond1992 in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 09-23-2015, 09:20 PM
  3. [SOLVED] $ Total a Quantity from Lowest to Highest Price
    By uberathlete in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-17-2015, 08:57 PM
  4. Replies: 1
    Last Post: 12-03-2014, 01:43 PM
  5. [SOLVED] Lowest price & Lowest lead time
    By thup_98 in forum Excel - New Users/Basics
    Replies: 11
    Last Post: 01-22-2013, 03:21 PM
  6. Replies: 3
    Last Post: 01-03-2013, 12:32 AM
  7. Replies: 1
    Last Post: 09-03-2008, 01:11 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1