CODEWITHSUNDEEP
excelmax
spenc044
spenc044

Reputation: 29

Second Largest Value with criteria in excel

I'm trying to find the second largest value of a given criteria in excel. So the formula I was trying to use was

=LARGE(IF($B$1:$B$216=1,$F$1:$F$216,""),2).

The problem is that it is returning the second largest overall value, but I want the second largest value where column B is 1. Better Answers would be helpful.

Upvotes: 1

Views: 2581

Answers (1)

A.S.H
A.S.H

Reputation: 29332

You are using an array (CSE) formula and you did not enter it the key combination for array formuas. Edit the cell again and click Ctrl+Shift+Enter.

Or if you want a normal (non CSE) formula, use this:

=AGGREGATE(14,6,$F$1:$F$216/($B$1:$B$216=1),2)

Upvotes: 2

Related Questions