Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 04 Oct 2016 12:09:33 +0200How to get highest value using Libre Calchttps://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/ 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
MickSun, 02 Oct 2016 18:04:37 +0200https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/Answer by JohnSUN for <p>Hi,</p>
<p>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.</p>
<p>As a new member, I can't attach a jpg of what I mean, unfortunately, but here is a text description:-</p>
<p>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.</p>
<p>What I want is to put in Cell D1 (for example) the MAX() of the values in B1:B9, but only for (say) "Apples".</p>
<p>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.</p>
<p>Is there a reasonably simple way to do this without modifying (e.g. by sorting) the source data?</p>
<p>Thanks</p>
<p>Mick</p>
https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?answer=78531#post-id-78531 =MAXIFS(B1:B9;A1:A9;"apples")Sun, 02 Oct 2016 19:01:12 +0200https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?answer=78531#post-id-78531Comment by mick_d for <pre><code>=MAXIFS(B1:B9;A1:A9;"apples")
</code></pre>
https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?comment=78535#post-id-78535I see how this should work as I can find references in Excel fora about this funtion, but it isn't in my Libre Calc.Sun, 02 Oct 2016 20:10:59 +0200https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?comment=78535#post-id-78535Answer by mick_d for <p>Hi,</p>
<p>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.</p>
<p>As a new member, I can't attach a jpg of what I mean, unfortunately, but here is a text description:-</p>
<p>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.</p>
<p>What I want is to put in Cell D1 (for example) the MAX() of the values in B1:B9, but only for (say) "Apples".</p>
<p>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.</p>
<p>Is there a reasonably simple way to do this without modifying (e.g. by sorting) the source data?</p>
<p>Thanks</p>
<p>Mick</p>
https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?answer=78538#post-id-78538Hi,
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
MickSun, 02 Oct 2016 20:50:35 +0200https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?answer=78538#post-id-78538Comment by mark_t for <p>Hi,</p>
<p>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:-</p>
<p>=MAX(IF(A2:A13=D1,B2:B13))</p>
<p>Based on that, Libre Calc was happy when I entered this:-</p>
<p>=MAX(IF(A1:A9=D1,B1:B9,))</p>
<p>and executed it as an array formula by pressing Ctrl-Shift-Enter.</p>
<p>Not sure if this is the best way, but it got me what I want. I'm a happy boy now.</p>
<p>Thanks</p>
<p>Mick</p>
https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?comment=78542#post-id-78542I 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.Sun, 02 Oct 2016 22:10:04 +0200https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?comment=78542#post-id-78542Comment by mick_d for <p>Hi,</p>
<p>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:-</p>
<p>=MAX(IF(A2:A13=D1,B2:B13))</p>
<p>Based on that, Libre Calc was happy when I entered this:-</p>
<p>=MAX(IF(A1:A9=D1,B1:B9,))</p>
<p>and executed it as an array formula by pressing Ctrl-Shift-Enter.</p>
<p>Not sure if this is the best way, but it got me what I want. I'm a happy boy now.</p>
<p>Thanks</p>
<p>Mick</p>
https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?comment=78605#post-id-78605If 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.Mon, 03 Oct 2016 18:39:01 +0200https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?comment=78605#post-id-78605Comment by mark_t for <p>Hi,</p>
<p>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:-</p>
<p>=MAX(IF(A2:A13=D1,B2:B13))</p>
<p>Based on that, Libre Calc was happy when I entered this:-</p>
<p>=MAX(IF(A1:A9=D1,B1:B9,))</p>
<p>and executed it as an array formula by pressing Ctrl-Shift-Enter.</p>
<p>Not sure if this is the best way, but it got me what I want. I'm a happy boy now.</p>
<p>Thanks</p>
<p>Mick</p>
https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?comment=78614#post-id-78614I'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.Mon, 03 Oct 2016 21:11:19 +0200https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?comment=78614#post-id-78614Answer by pierre-yves samyn for <p>Hi,</p>
<p>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.</p>
<p>As a new member, I can't attach a jpg of what I mean, unfortunately, but here is a text description:-</p>
<p>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.</p>
<p>What I want is to put in Cell D1 (for example) the MAX() of the values in B1:B9, but only for (say) "Apples".</p>
<p>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.</p>
<p>Is there a reasonably simple way to do this without modifying (e.g. by sorting) the source data?</p>
<p>Thanks</p>
<p>Mick</p>
https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?answer=78530#post-id-78530Hi
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](/upfiles/1475427409600562.ods)
RegardsSun, 02 Oct 2016 18:56:52 +0200https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?answer=78530#post-id-78530Comment by pierre-yves samyn for <p>Hi</p>
<p>If I understand well you can use in E1: <code>MAXIFS(B1:B9; A1:A9; D1)</code></p>
<ul>
<li>In D1 the searched fruit (e.g. Apples)</li>
<li>B1:B9 the values</li>
<li>A1:A9 fruit names</li>
</ul>
<p>See <a href="/upfiles/1475427409600562.ods">MaxIfs.ods</a></p>
<p>Regards</p>
https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?comment=78645#post-id-78645Sorry, I forgot to mention that it is a new function, added in [5.2](https://wiki.documentfoundation.org/ReleaseNotes/5.2#New_spreadsheet_functions)Tue, 04 Oct 2016 12:09:33 +0200https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?comment=78645#post-id-78645Comment by mick_d for <p>Hi</p>
<p>If I understand well you can use in E1: <code>MAXIFS(B1:B9; A1:A9; D1)</code></p>
<ul>
<li>In D1 the searched fruit (e.g. Apples)</li>
<li>B1:B9 the values</li>
<li>A1:A9 fruit names</li>
</ul>
<p>See <a href="/upfiles/1475427409600562.ods">MaxIfs.ods</a></p>
<p>Regards</p>
https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?comment=78534#post-id-78534Hi,
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.
ThanksSun, 02 Oct 2016 20:10:21 +0200https://ask.libreoffice.org/en/question/78528/how-to-get-highest-value-using-libre-calc/?comment=78534#post-id-78534