+ Reply to Thread
Results 1 to 3 of 3

Dynamic range bottom to top formula.

  1. #1
    Registered User
    Join Date
    04-19-2020
    Location
    CA. USA
    MS-Off Ver
    office 365
    Posts
    2

    Dynamic range bottom to top formula.

    I am working on a buy/hold/sell indicator for the stock market. I have a tricky formula to code. What I need is to determine when the last order to buy or sell was. The newest dates are on the bottom. If yesterday was a buy then use that info to calculate the formula, but if it is a hold, then I need the range recorded until there was a buy or sell order. If it was a buy order then I need the max(dynamic range). If it was a sell order then I need the min(dynamic range).

    So it should look like this.

    A B C D
    1 Buy/Sell Close ARC Change Action
    2 Buy 13 1 Buy (Calculated by formula)
    3 Hold 13.5 1.2
    4 Hold 13.7 1
    6 ? 12 1.1 Formula
    Formula(If last change action was a Buy then IF(B5>Max(A2:A4)-C5),A6="Hold",A6="Sell") If it was a Sell order then IF(B5<Min(A2:A4)+C5,A6="Hold",A6="Buy") Also if it is a hold then leave D6 blank. I'm assuming it will need to be blank to use the counta function to figure out how far up to go when calculating the dynamic range. If not then this formula could take place in column A if you can figure out how to do it. I hope this is specific enough. If not ask me questions!

    A2:A4 needs to be dynamically calculated based on how far it has to go to detect that change action.

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,606

    Re: Dynamic range bottom to top formula.

    Hi,

    If in one column you would have date and time of the event then finding newest one will be an easy task.

    My sincere suggestion is: Please read the yellow banner at the top of the page. Or skip to extended version of it below:

    Will you please attach a SMALL sample Excel workbook(s) (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

    1. Make sure that your sample data are truly REPRESENTATIVE of your real data. (If there are typical cases like: all unique values/duplicates could occur, day/night, nobody present/several persons at once, before/on/past due, empty cells between data, mixed text/numbers, etc. - please show them all, or at least indicate in text). The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

    2. Make sure that your desired solution(s) is/are also shown (mock up the results manually).

    3. Make sure that all confidential/restricted information (either personal or business) like real e-mails, social security numbers, bank accounts, etc. is removed first!!

    To attach an Excel file you have to do the following (paperclip icon does not work!): Just before posting, scroll down and press Go Advanced button and then scroll down and press Manage Attachments link. Now follow the instructions at the top of that pop-up screen.
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    04-19-2020
    Location
    CA. USA
    MS-Off Ver
    office 365
    Posts
    2

    Re: Dynamic range bottom to top formula.

    Here is the worksheet I'm working on.
    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] Return Row no. for Lookup from bottom of range, ignoring bottom n no. of rows
    By naira in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 12-27-2018, 12:07 AM
  2. Lookup from bottom of range, ignoring bottom n no. of rows
    By naira in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 12-05-2018, 04:26 AM
  3. [SOLVED] Select Dynamic Range from bottom up
    By taisweb in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-07-2016, 08:56 AM
  4. Thick Bottom Border for Dynamic Print Range
    By Robecca in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-21-2014, 12:04 PM
  5. Formula to range from bottom to top
    By xladept in forum The Water Cooler
    Replies: 1
    Last Post: 09-19-2014, 04:52 PM
  6. [SOLVED] removing blank at bottom of dynamic named range
    By HeyInKy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-12-2014, 01:47 PM
  7. dynamic shifting range, always at bottom of column
    By buckfran in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 08-13-2009, 07:14 PM

Tags for this Thread

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