Q: Finding min/max number within a selected date range - Please HELP!
Hi all,
I am not very advanced at complex formulas however using this forum over the past few weeks thanks to everyone here, I have been able to create amazing automated data collection workbook. I have been searching for the final piece missing and from the two explanations I have found here so far, they are too complex for me to understand yet what I am wanting to achieve I thought was quite straight forward.
If anyone can help me with the formula to use in these empty boxes it will make my week!
FYI I am using a mac air that is all up to date.
Thanks.
Posted on Oct 2, 2016 8:58 PM
Hi no...
Strange user name. If true, I hope this name is soon outdated.
While (s)he hasn't answered your question, Dracik has provided a clue to the type of function needed. Unfortunately, although Numbers supports SUMIF, COUNTIF and AVERAGEIF (and the plural versions of the first two), it does not support MAXIF, MINIF, or the functions that would be really useful here, MAXIFS and MINIFS.
There are ways to accomplishs this, though. Here's one:
Table 1:
Column A: A list of dates in chronological order.
Column B: The 'month number' for each date, extracted by the formula below. This column is necessary to the working of the process, but does not need to be 'column B,' and may be hidden. As this table is for data entry, this calculation should probably be placed in column A, and hidden from the user to avoid accidentally damaging the formula.
Column C: The number associated with each date in column A, and from which the minimum and maximum values for each month are to be retrieved. The cells are coloured manually as an aid to locating the group of values for each month.
Formula:
B2, and filled down to the last row of the table: =MONTH(A2)
Table 2
Column A: entered data. The names of the months, in calendar order.
Column B: entered data. The number of the month corresponding to the name in column A.
Columns C and S: Formulas to calculate the Minimum and Maximum value for each month.
The two formulas are identical except for the use of MIN() in column C and MAX() in column D.
Base formulas (These will return an error message for months in which not data exists)
C2: =MIN(OFFSET(Table 1::$A$1,MATCH($B2,Table 1::$B,0)−1,2,COUNTIF(Table 1::$B,$B2)))
D2: =MAX(OFFSET(Table 1::$A$1,MATCH($B2,Table 1::$B,0)−1,2,COUNTIF(Table 1::$B,$B2)))
To prevent error messages in months where no data exists, IF has been used as a 'switch' to prevent calculation until at least one line of data exists for the month on 'this row'.
C2: =IF(COUNTIF(Table 1::$B,$B2)<1,"",MIN(OFFSET(Table 1::$A$1,MATCH($B2,Table 1::$B,0)−1,2,COUNTIF(Table 1::$B,$B2))))
D2: =IF(COUNTIF(Table 1::$B,$B2)<1,"",MAX(OFFSET(Table 1::$A$1,MATCH($B2,Table 1::$B,0)−1,2,COUNTIF(Table 1::$B,$B2))))
Both formulas are filled down to the December row of their respective columns.
Notes:
Dates in Table 1 MUST be grouped by month.
The table will handle one year's worth of data.
Regards,
Barry
Posted on Oct 3, 2016 1:26 AM

