Ask Your Question
0

How to get highest value using Libre Calc

asked 2016-10-02 18:04:37 +0200

mick_d gravatar image

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 flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted
0

answered 2016-10-02 19:01:12 +0200

JohnSUN gravatar image
=MAXIFS(B1:B9;A1:A9;"apples")
edit flag offensive delete link more

Comments

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.

mick_d gravatar imagemick_d ( 2016-10-02 20:10:59 +0200 )edit
0

answered 2016-10-02 20:50:35 +0200

mick_d gravatar image

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

edit flag offensive delete link more

Comments

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.

mark_t gravatar imagemark_t ( 2016-10-02 22:10:04 +0200 )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.

mick_d gravatar imagemick_d ( 2016-10-03 18:39:01 +0200 )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.

mark_t gravatar imagemark_t ( 2016-10-03 21:11:19 +0200 )edit
0

answered 2016-10-02 18:56:52 +0200

pierre-yves samyn gravatar image

updated 2016-10-02 18:57:49 +0200

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

edit flag offensive delete link more

Comments

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

mick_d gravatar imagemick_d ( 2016-10-02 20:10:21 +0200 )edit

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

pierre-yves samyn gravatar imagepierre-yves samyn ( 2016-10-04 12:09:33 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-10-02 18:04:37 +0200

Seen: 5,733 times

Last updated: Oct 02 '16