View Full Version : Minimum and Maximum value in a quarter based on a date

ice_28

2011-07-08, 09:11 AM

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

Ken Puls

2011-07-08, 05:21 PM

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.

ice_28

2011-07-09, 03:09 AM

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:

ice_28

2011-07-11, 05:18 AM

Thank you all. The problem has been solved

Ken Puls

2011-07-12, 05:03 AM

Can you post your solution? Might help someone else one day. ;)

ice_28

2011-07-12, 07:32 AM

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)

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.