Forum Discussion

maggersmentions's avatar
maggersmentions
Copper Contributor
Nov 20, 2022

Can I use the LARGE function with MAXIFS?

Hi, I'm using the MAXIFS to extract data from a large spreadsheet. To make it more accurate I want to ignore the largest item from the data. 

 

Can I use the LARGE function with the MAXIFS formula? 

Formulas and Functions

3 Replies

Sort By
  • Viz's avatar
    Viz
    Brass Contributor
    Nov 20, 2022
    Can you clarify whether you want to ignore the largest item in the entire data set (irrespective of whether it meets condition or not) or whether you want to get the second largest value that meets the condition?

    The first one is pretty easy: = Maxifs(Val_Rng, Val_Rng, "<"&Max(Val_Rng), [critieria column2], critieria2 ....)

    If it is the second case, the if you are using Office 365, life is easy with a filter function:

    =Large(Filter(Val_Rng, (condition1)*(condition2)*...), 2)
    • maggersmentions's avatar
      maggersmentions
      Copper Contributor
      Nov 21, 2022
      Hi, thank you for your quick response. In the dataset I am using some of the maximum (and minimum) values skews the calculation so I was going to ignore those items. I am assuming that using the second largest will improve the results but if not I will reitterate the process with the third largest. Hope this helps. Many thanks.
  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Nov 20, 2022

    maggersmentions 

    =LARGE(IF(($A$3:$A$20=E5)*(YEAR($B$3:$B$20)=F5),$C$3:$C$20),2)

    If you want the 2nd largest result with certain conditions you can try LARGE and IF. If you don't work with Office365 or Excel 2021 or Excel online you have to enter the formula with ctrl+shift+enter.

     

Resources