How to get highest value using Libre Calc

English
,
#1

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

#2

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

#3

Hi,

I downloaded your spreadsheet, but it errors against cell E1, which contains"=com.microsoft.maxifs(B1:B9, A1:A9, D1)".

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

#4

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

#5 
=MAXIFS(B1:B9;A1:A9;"apples")
#6

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.

#7

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

#8

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.

#9

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.

#10

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.

#11

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.