[Solved] Find largest conditional value

Discuss the spreadsheet application

[Solved] Find largest conditional value

Postby Alexie » Sat Jan 12, 2019 5:59 pm

Hi,

I have come across a problem which I cannot seem to solve on my own. I have the following situation:
ID Value
A 1.5
A 1.5
B 2.5
A 4

And I would like to select the largest value for a given value:
ID Value
A 4
B 2.5

Any suggestions to achieve this by code?
Thanks in advance
Last edited by Alexie on Sat Jan 12, 2019 7:49 pm, edited 1 time in total.
OpenOffice 4.1.1 on Windows 10
Alexie
 
Posts: 2
Joined: Sat Jan 12, 2019 5:51 pm

Re: Find largest conditional value

Postby FJCC » Sat Jan 12, 2019 6:24 pm

One way to do this is with a Pivot Table (menu item Data -> Pivot Table). Right click on the table in the attached document and select Edit Layout to see how I set it up. You can double click on the item that says Max Value to see other options for calculating statistical properties of the Value column.
Attachments
MaxByID.ods
(8.83 KiB) Downloaded 3 times
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7007
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Find largest conditional value

Postby FJCC » Sat Jan 12, 2019 6:32 pm

You can also use an array formula. Enter the formula
Code: Select all   Expand viewCollapse view
=MAX(IF(A2:A10 = "a";B2:B10;""))

but finish by pressing CTRL+Shift+Enter. The formula will then be enclosed in curly braces {}.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7007
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Find largest conditional value

Postby Zizi64 » Sat Jan 12, 2019 6:45 pm

Tibor Kovacs, Hungary; LO4.4.7, LO6.1.4 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.1.4 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7684
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Find largest conditional value

Postby Alexie » Sat Jan 12, 2019 7:33 pm

I tried the suggested solution in the above thread.
It works! Many thanks for your replies!
OpenOffice 4.1.1 on Windows 10
Alexie
 
Posts: 2
Joined: Sat Jan 12, 2019 5:51 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 15 guests