Date: prev next · Thread: first prev next last
2021 Archives by date, by thread · List index

Bravo Remy,

On 2021/05/29 20:29, Remy Gauthier wrote:

I created something that ressembles what Johnny created, and I understand your data is formatted like so:

Data1 Data2 blank Data3 Data4 blank Data5 Data6

You want Data6 to be the maximum of Data2 and Data4 (and possibly more columns as well), and you want to have Data5 equal to the date on which the maximum occurred.

As Johnny indicated, the formula to place in Data6 is =MAX(Bx;Ex) where x is the row number.

If you want to use MATCH to find the date, then you shoud proceed like so:

The MATCH() function takes 3 arguments:
- Value to search
- Array where to search
- How to search
This function will return where the value you are looking for is located in the search array. The "how to search" argument tells the function if the values in the array are sorted (1 or -1) or not (0). In your case, the values are not sorted so you will need to use 0. Note that if you use 1 or -1, the search will return the closest match,and will not fail if the value you are looking for does not exist in your search array; if you use 0, however, the search will fail if your value is not in the array. I will usually always use zero (exact match) regardless of how the data is sorted. The only time I will make use of the 1 or -1 values is when I need to interpolate in a series of data points and I need to find where the interpolation will take place in the dataset.

The first argument will be Data6. The second argument will be Ax:Ex, where x is the row number. You can use the entire row like this because:
- Date values will always be greater than the rain values you have
- Empty cells do not count
The third argument will be zero since you want an exact match in an array that is not sorted.

The output of the MATCH() function will be the column number of the maximum (since the first cell of the search range is Ax). The date is 1 to the left: one column less, so MATCH()-1 will give the column where we can find the date.

To extract the date with INDEX(), you must use the same range as used to MATCH() the value. The arguments are:
- Cell range
- Row in the range (1 if you only select one row of data)
- Column in the range

The cell range will be Ax:Ex (exactly what was used in the MATCH() function), the row will be 1 (only 1 row of data), and the column will be the result of the MATCH() we did minus 1. This means the formula in Gx will be:


I hope this helps.

Works like a charm. I mostly understood your explanation too. and will keep reviewing it until it sinks in

I copy and pasted your formula and then 'reinvented' it to cover the correct ranges and lines in the Data sheet.

Now the job is to incorporate the year into the date.


To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
Privacy Policy:


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.