• 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.

MAXIF Finding Max Value for Group in Column

johncharles1923

New Member
Hi - Thanks for helping. Trying to come up with a formula that will look at Column E groupings and give me the largest number in column G (for said grouping) in column H with an "X". I think it's a MAXIFS but can't quite finish the formula.

73863
 
Thanks for your help! And noted on the sample file. I tried the formula and it works! Just need the data to populate on the row with the largest value. Looks like it defaults to the first brand/group value from Column E.

73870
 

Attachments

  • MAXIFS Example.xlsx
    10.2 KB · Views: 9
The formulas didn't just default to output the result against the first row of each block; they were carefully crafted to achieve that!
To bring the value forward on the same row
= IF( MAXIFS(Cases, Brand, Brand)=Cases, Cases, "")
 
Just a different way of working. I never use filters (or stripy tables come to that).
Their use tends to mess up the appearance and balance of the worksheet and I prefer having both the original and filtered tables visible.

73886

73888
 
An alternative solution is with Get and Transform

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Year", Int64.Type}, {"Brand", type text}, {"UOM", type text}, {"Cases", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Brand"}, {{"Max", each List.Max([Cases]), type nullable number}})
in
    #"Grouped Rows"
 

Attachments

  • MAXIFS Example.xlsx
    25.6 KB · Views: 6
Back
Top