Largest If?

[Robindude]

I have a data set which has two columns of interest for this issue, which we'll call A and R (since they aren't adjacent in the listing right now).
Column A contains numbers or "N/A" if the field is invalid (based on other data, why it's that way isn't important).
Column R contains "Y" or "N" to designate a condition about the item represented by the row it's on.

What I need:

1) The largest number in column A but only counting those lines where the corresponding R column is "Y".
2) The largest number in column A but only counting those lines where the corresponding R column is "N".
(The above two are separate things.)

So if you had:
A R
5 Y
2 Y
3 N
4 N
1 Y
4 N

Request 1 returns 5, request 2 returns 4 (because 4 is the largest number where R is also "N", so even though the first row has a 5 in it, R is "Y" so it isn't counted).
Any ideas?

[Villeroy]

=MAX(IF(R1:R6="Y";A1:A6;"")) entered as array formula with Ctrl+Shift+Enter

[Zizi64]

It is achievable with the MAXIFS() function.
The MAXIFS() function exists in the LibreOffice Calc, but not exist in the AOO Calc.


One more approach: you can write a macro function for this task.

[robleyd]

Note: the formulae below are array formula and need to be entered in the input line, then press Ctrl-Shift-Enter - they will then appear enclosed in {} curly braces. Assuming values in A1 to A6 and conditions in R1 to R6.

For Y:

Code: Select all

=MAX(IF(R1:R6="Y";A1:A6))

and for N

Code: Select all

=MAX(IF(R1:R6="N";A1:A6))