# Regarding Minimum Value

#### Ram Mher

##### Member
Dear Sir,s

I want to find minimum value for each year . Please support on attached Sample data :-

Year Min
2002 Result shown in excel sheet
2003

#### Attachments

• 65.9 KB Views: 7

#### Peter Bartholomew

##### Well-Known Member
This is unashamedly an Excel 365 only solution
Code:
``````= LET(
startdate, DATE(yr,1,1),
enddate, DATE(yr+1,1,0),
yrMinV,
MINIFS(Value,
Date,">+"&startdate,
Date,"<="&enddate),
yrMaxV,
MAXIFS(Value,
Date,">+"&startdate,
Date,"<="&enddate),
IF({1,0}, yrMinV, yrMaxV)  )``````

#### Attachments

• 66.1 KB Views: 6

#### pecoflyer

##### Active Member
If you don't have the MINIF/MAXIF functions, use the AGGREGATE function

#### Attachments

• 65.4 KB Views: 9

#### vletm

##### Excel Ninja
Ram Mher
If You want to solve those without formulas ...

#### Attachments

• 88.6 KB Views: 10

#### pecoflyer

##### Active Member
There is more than one way to skin a cat ...

#### AlanSidman

##### Well-Known Member
and yet one more way with Power Query

Code:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Value", type number}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Grouped Rows" = Table.Group(#"Inserted Year", {"Year"}, {{"Min", each List.Min([Value]), type nullable number}, {"Max", each List.Max([Value]), type nullable number}})
in
#"Grouped Rows"``````

#### Attachments

• 80.3 KB Views: 2

#### Peter Bartholomew

##### Well-Known Member
Nice selection of methods! I had to look to see whether @pecoflyer had gone with
= AGGREGATE(15, 6, Value/(YEAR(Date)=@yr), 1)
or
= AGGREGATE(15, 4, IF(YEAR(Date)=@yr, Value), 1)

I might play with a LAMBDA function later to see whether
= YrMIN(@yr)
can be made to work with the entire array 'yr'.

#### Peter Bartholomew

##### Well-Known Member
I made it work, but it looks nothing like a spreadsheet solution!

The catch is that neither MINIF nor MAXIF exist as functions, so I had to define them. Worse still is the fact that the MAP function which applies an array parameter term by term to a Lambda function, though promised, has yet to appear. The MAXIF and MINIF functions

Code:
``````= LAMBDA(val,crit,
LAMBDA(yr,
MAX(IF(crit=yr,val))
))

= LAMBDA(val,crit,
LAMBDA(yr,
MIN(IF(crit=yr,val))
))``````
are reasonably succinct, though there is the catch that the parameters
(Value, YEAR(Date))(2005)
are passed to the function in two stages. The first step leaves a lambda function that may itself be passed as a parameter and the second step is to allow MAP to feed the final parameter term by term. My version of MAP was

Code:
``````= LAMBDA(fn,y,
LET(
n, COUNTA(y),
ℓ, INDEX(y,n),
m, fn(ℓ),
k, SEQUENCE(1,n-1),
residual, INDEX(y,k),
return, IF(n>1, MAP(fn,residual), 0),
IF(n>1, XSTACK(return,m), m) ) )``````
which calls itself recursively, but with one term removed at each call until nothing remains. Each time the function returns the values calculated at that level of the memory stack are available and a final function XSTACK is used to gather the individual results into the result array.

Code:
``````= LAMBDA(A,v,
LET(
n, COUNTA(A),
k, SEQUENCE(n+1),
IF(k<=n,A,v))
)``````
As stated earlier, to me this looks nothing like spreadsheets as they are understood. Nevertheless they are valid Excel formulas.
[the macro simply loads formulas from the spreadsheet into Name Manager as the .RefersTo property of a Name]

#### Attachments

• 86.2 KB Views: 3