Finding the largest value depending on value in another column

[Hozz]

Hi guys,

if using a normal =LARGE(F1:F85,1) to find the largest value in column F. This is all fine and dandy and works the way it's supposed to.
I need to modify this in such a way though to take into account, the values in column C.
let me explain...

Column C will not always have an entry in the row that has the largest number in Column F.
So I would like the Large command to find the largest entry in Column F but that same row has to have an entry in column C otherwise it's ignored.

so if we have this -

.....C...D...E...F
1....2............4
2..................5
3....1............2
4..................3
5..................1

The largest entry in column F should be 4, because I have an entry in column C.
The second largest entry in column F should be 2 because there's an entry in column C.

I've stumped myself on this one...help!

 

[Marcelo Branco]

Try this array-formula

=LARGE(IF($C$1:$C$85<>"",$F$1:$F$85),1)

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

HTH

M.

 

[Hozz]

ah. The a dreaded CSE formula. I would never have got that...I haven't a clue how they work!

Thanks!