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?
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.
[SOLVED] MAXIFS help
[SOLVED] MAXIFS help
- 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
Reason: Add green tick
Suite: LibreOffice 7.5.12 ||| OS: Windows 10.0 Build 19045 ||| Calc: threaded ||| CPU threads: 8
Re: MAXIFS help
An aggregation (calculating max, min, sum, average, count etc. for chosen categories) requires a flat list.
With a flat list you can use all kinds of functions, pivot tables, filters etc
Code: Select all
Cost Price Check
Cost1 1
Cost2 1,06 1
Cost3 2
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: MAXIFS help
Villeroy wrote:An aggregation (calculating max, min, sum, average, count etc. for chosen categories) requires a flat list.With a flat list you can use all kinds of functions, pivot tables, filters etcCode: Select all
Cost Price Check Cost1 1 Cost2 1,06 1 Cost3 2
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
Re: MAXIFS help
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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: MAXIFS help
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