• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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)  )
74177
 

Attachments

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

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!

74189

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

Top