I currently use the following formula to find the largest number in a dataset (Sheet1K7:K106), conditional on some corresponding values (Sheet2!C7:C106) being less than a certain number (Sheet3!C18):
我目前使用以下公式来查找数据集中最大的数字(Sheet1K7:K106),条件是某些相应的值(Sheet2!C7:C106)小于某个数字(Sheet3!C18):
=MAX(Sheet1!K7:K106*(Sheet2!C7:C106<Sheet3!C18))
I would like to now find the second largest number given the same condition. I would assume that this requires the LARGE function, although I can't seem to apply the aforementioned condition.
我现在想找到同样条件下的第二大数字。我认为这需要LARGE功能,虽然我似乎无法应用上述条件。
1 个解决方案
#1
3
If you're using Excel 2010 or later:
如果您使用的是Excel 2010或更高版本:
=AGGREGATE(14,6,Sheet1!K7:K106/(Sheet2!C7:C106<Sheet3!C18),2)
Otherwise, array formula**:
否则,数组公式**:
=LARGE(IF(Sheet2!C7:C106<Sheet3!C18,Sheet1!K7:K106),2)
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
**数组公式的输入方式与“标准”公式的输入方式不同。您只需按住CTRL和SHIFT键,然后按ENTER键,而不是只按ENTER键。如果你已经正确地完成了它,你会注意到Excel在公式周围放置了大括号{}(尽管不要尝试自己手动插入这些)。
#1
3
If you're using Excel 2010 or later:
如果您使用的是Excel 2010或更高版本:
=AGGREGATE(14,6,Sheet1!K7:K106/(Sheet2!C7:C106<Sheet3!C18),2)
Otherwise, array formula**:
否则,数组公式**:
=LARGE(IF(Sheet2!C7:C106<Sheet3!C18,Sheet1!K7:K106),2)
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
**数组公式的输入方式与“标准”公式的输入方式不同。您只需按住CTRL和SHIFT键,然后按ENTER键,而不是只按ENTER键。如果你已经正确地完成了它,你会注意到Excel在公式周围放置了大括号{}(尽管不要尝试自己手动插入这些)。