Results 1 to 6 of 6

Thread: Minimum and Maximum value in a quarter based on a date

  1. #1

    Minimum and Maximum value in a quarter based on a date



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Every business day I enter a date and a correspondingnumeric value.

    When I enter the first entry of a new quarter, that shouldbe a trigger to calculate the maximum value of the previous quarter, theminimum value of the previous quarter and the last value of the previousquarter.

    Each quarter does not have the same number of days/entries –they vary. Quarters go as follows: 1Jan-31 March (Q1), 1 Apr – 30 Jun (Q2), 1 Jul – 30 Sep (Q3), 1 Oct -31 Dec (Q4).

    Can you please help me calculate the maximum, minimum andlast price of the previous quarter – without a VBA code? Instead, I’d prefer using formulas andfunctions.
    An excel file with some sample data is attached

    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Try this and see if it works. The final column uses NA() to show an error unless it is the last day of the quarter. (I wasn't sure how you'd want to deal with it on an ongoing basis.)

    To be fair, there are other ways to do this as well. A pivot table would also work great, but you'd need to add a "Year" column to your data as well so that you could filter it by year. The only difficult part then is pulling the closing balance.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3

    Minimum and Maximum value in a quarter based on a date

    Thank you sending me the file. The last close price of the previous quarter works great.
    However, the maximum and minimum prices require a revision. I've attached your file with some minor addition, hopefully clarifying the logic behind calculating the max and min values:

    On a turn of a new quarter (row 47), I need the maximum value of column C for Quarter 2 to be calculated, and also the minimum value of column D for Quarter 2. Those 2 values (Max and Min) are then to be copied for the entire duration of the quarter 3. Once the quarter 4 starts, the new set of maximum and minimum values are to be calculated based on quarter 3 values. And so on. The problem is, quarters can have different number of days, so we can't use a constant range.

    Please help
    Attached Files Attached Files

  4. #4
    Thank you all. The problem has been solved

  5. #5
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Can you post your solution? Might help someone else one day.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  6. #6

    If your Dates are in column A, and your numbers are in column B, then I would first NAME your ranges, using dynamic names. The following assumes a label in row 1, and dates extending down from A2; no empty rows, and nothing else in column A. Column B contains your numbers.
    Define Name:
    Dates refers to: =OFFSET($A$1,1,0,COUNT($A:$A),1)
    Numbers refers to: =OFFSET(Dates,0,1)
    Then these formula must be **array-entered**:
    You should have entered the formula into G3; then confirmed it with <ctrl><shift><enter>; and then copy/drag or fill down into the remaining cells.
    Instead, what you did was select the entire range; entire the formula into the active cell (G2), and then confirmed with <ctrl><shift><enter>. That enters the same formula into the entire range as an array, rather than having individual array-entered formulas that are adjusting for each date in column A.

    Do NOT select the range until AFTER you have entered and confirmed the formula into a single cell.
    So, for example, given this slightly modified formula to fit with your layout:
    MAX((Dates>=DATE(YEAR(A2),(INT((MONTH(A2)-1)/3)+1)*3-5,1))*(Dates<=DATE(YEAR(A2),(INT((MONTH(A2)-1)/3)+1)*3-2,0))*Numbers)
    Select and copy the above formula
    Select G2 on your worksheet
    Paste (the formula into G2)
    <ctrl><shift><enter>
    Observe the braces around the formula and the result of zero.
    Fill-Down to the last date either by "dragging" down; or by selecting the entire range G2:Gn and then <ctrl-d>. Observe that the cell references adjust for each line (eg A2 becomes A3 in G3).

    For the "MIN" formula, I used IFERROR to return a zero instead of the error when there is no previous quarter. This function is only present in XL2007 and later.
    H2:
    =IFERROR(LARGE((Dates>=DATE(YEAR(A2),(INT((MONTH(A2)-1)/3)+1)*3-5,1))* (Dates<=DATE(YEAR(A2),(INT((MONTH(A2)-1)/3)+1)*3-2,0))*
    Numbers,SUM((Dates>=DATE(YEAR(A2),(INT((MONTH(A2)-1)/3)+1) *3-5,1))*(Dates<=DATE(YEAR(A2),(INT((MONTH(A2)-1)/3)+1)*3-2,0)))),0)



Posting Permissions

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