# Minimum and Maximum value in a quarter based on a date

#### ice_28

##### New member
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

Staff member
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
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.

#### Attachments

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

#### ice_28

##### New member
Thank you all. The problem has been solved

#### Ken Puls

Staff member
Can you post your solution? Might help someone else one day.

#### ice_28

##### New member

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