Minimum and Maximum value in a quarter based on a date

ice_28

New member
Joined
Jul 8, 2011
Messages
5
Reaction score
0
Points
0
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

 

Attachments

  • Excel example problem.xlsx
    26.4 KB · Views: 53

Ken Puls

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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.
 

Attachments

  • xlgf263-1.xlsx
    31.8 KB · Views: 105

ice_28

New member
Joined
Jul 8, 2011
Messages
5
Reaction score
0
Points
0
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:confused2:
 

Attachments

  • xlgf263-1.xlsx
    29.8 KB · Views: 58

ice_28

New member
Joined
Jul 8, 2011
Messages
5
Reaction score
0
Points
0

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)


 
Top