Forum Discussion
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?...
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)
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)
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.