[SOLVED] MAXIFS help

Discuss the spreadsheet application
Post Reply
User avatar
AWoodShed
Posts: 53
Joined: Mon Aug 19, 2019 1:49 am
Location: Australia

[SOLVED] MAXIFS help

Post by AWoodShed »

Hi everyone,

I've recently learned how to use MAXIFS, to calculate maximum values of adjacent values, based on specific criteria.

However, this only seems to work if the Max-range & Criteria-range are both in adjacent cells.

My data is spread out in non-adjacent cells (either rows or columns), so I can't get the MAXIFS formula to calculate properly.

Scenario: I have multiple cost prices for an item, from different suppliers.
Some are marked to Include in the MAX calculation (with a "y" in an adjacent column), and others are marked NOT to include.

I want to calculate the Max value of all cost prices, if it is flagged to include the price in the listing.

How can I achieve this, without having to use a "helper sheet" to first create a new matrix, so the cost-price values ARE in adjacent cells?
MAXIFS example.png

Example shown above the grey separator line, works as expected, since the values for Max-range & Criteria-range are both in adjacent cells.

My data is formatted as-per under the grey bar and cannot be modified.
A MAXIFS formula will not work with non-adjacent data sets.

Example spreadsheet also attached.
Attachments
MAXIFS example.ods
(16.39 KiB) Downloaded 142 times
Last edited by robleyd on Tue Mar 23, 2021 3:58 am, edited 2 times in total.
Reason: Add green tick
Suite: LibreOffice 7.5.12 ||| OS: Windows 10.0 Build 19045 ||| Calc: threaded ||| CPU threads: 8
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: MAXIFS help

Post by Villeroy »

An aggregation (calculating max, min, sum, average, count etc. for chosen categories) requires a flat list.

Code: Select all

Cost	Price	Check
Cost1	1	
Cost2	1,06	1
Cost3	2	
With a flat list you can use all kinds of functions, pivot tables, filters etc
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
AWoodShed
Posts: 53
Joined: Mon Aug 19, 2019 1:49 am
Location: Australia

Re: MAXIFS help

Post by AWoodShed »

Villeroy wrote:An aggregation (calculating max, min, sum, average, count etc. for chosen categories) requires a flat list.

Code: Select all

Cost	Price	Check
Cost1	1	
Cost2	1,06	1
Cost3	2	
With a flat list you can use all kinds of functions, pivot tables, filters etc

I'm not quite sure what you're meaning?
Are you saying it's simply not possible for what I need to achieve, based on my current format?
And that I need to use a helper-sheet to create the data in adjacent cells? (I assume this is what you mean by a "flat list")
Suite: LibreOffice 7.5.12 ||| OS: Windows 10.0 Build 19045 ||| Calc: threaded ||| CPU threads: 8
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: MAXIFS help

Post by Villeroy »

Your data layout is too complicated. All the features and functions of Calc require a simple columnar layout
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
AWoodShed
Posts: 53
Joined: Mon Aug 19, 2019 1:49 am
Location: Australia

Re: MAXIFS help

Post by AWoodShed »

Villeroy wrote:Your data layout is too complicated. All the features and functions of Calc require a simple columnar layout

Thank you.

I've amended my data to include helper-columns to assist with this now :)
Suite: LibreOffice 7.5.12 ||| OS: Windows 10.0 Build 19045 ||| Calc: threaded ||| CPU threads: 8
Post Reply