# How to get highest value using Libre Calc [closed]

Hi,

I am trying to work out how I can achieve a MAX() type selection on a range of cells, but limit the cells over which I perform the MAX() according to the value of the adjacent cell.

As a new member, I can't attach a jpg of what I mean, unfortunately, but here is a text description:-

Cells A1:A9 contain a random mix of "Apples", "Oranges" and "Bananas". Each cell in B1:B9 contain a number of fruit of the type contained in the adjacent "A" cell.

What I want is to put in Cell D1 (for example) the MAX() of the values in B1:B9, but only for (say) "Apples".

If I do MAX(B1:B9), I (rightly) get the simple highest value, regardless fo the type of fruit. What I need is some way to filter on the fruit and MAX() only matching cells.

Is there a reasonably simple way to do this without modifying (e.g. by sorting) the source data?

Thanks

Mick

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2020-09-13 08:03:50.546811

Sort by » oldest newest most voted

Hi,

I did some digging prompted by JohnSUN and pierre-yves's answers and found this formula on an Excel forum when looking for the MAXIFS() function:-

=MAX(IF(A2:A13=D1,B2:B13))

Based on that, Libre Calc was happy when I entered this:-

=MAX(IF(A1:A9=D1,B1:B9,))

and executed it as an array formula by pressing Ctrl-Shift-Enter.

Not sure if this is the best way, but it got me what I want. I'm a happy boy now.

Thanks

Mick

more

I don't think you need to enter this as an array function, although it may depend on the version of LibreOffice. The IF function in this case returns an array, but then MAX takes the array and returns a single value.

( 2016-10-02 22:10:04 +0100 )edit

If I enter the formula as above and just press "Enter", it fails with "#VALUE!". If I press "Ctrl+Shift+Enter" it works. I'm using Libre Calc Version: 5.1.5.2 under Windows 7.

( 2016-10-03 18:39:01 +0100 )edit

I'm using LibreOffice 5.2.1.2 x64 on Windows 8.1. I think I needed Ctrl+Shift+Enter on some earlier version of LO but I forget which one.

( 2016-10-03 21:11:19 +0100 )edit
=MAXIFS(B1:B9;A1:A9;"apples")

more

I see how this should work as I can find references in Excel fora about this funtion, but it isn't in my Libre Calc.

( 2016-10-02 20:10:59 +0100 )edit

Hi

If I understand well you can use in E1: MAXIFS(B1:B9; A1:A9; D1)

• In D1 the searched fruit (e.g. Apples)
• B1:B9 the values
• A1:A9 fruit names

See MaxIfs.ods

Regards

more

Hi,

I assume the "com.microsoft.maxifs" is some external funtion (probably from excel?), but I don't have this on my machine. I see how it works, but I need to know how to get this funtion into Libre Calc.

Thanks

( 2016-10-02 20:10:21 +0100 )edit

Sorry, I forgot to mention that it is a new function, added in 5.2

( 2016-10-04 12:09:33 +0100 )edit