Bravo Remy,
On 2021/05/29 20:29, Remy Gauthier wrote:
Hello,
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:
=INDEX(Ax:Ex;1;MATCH(Hx;Ax:Ex;0)-1)
I hope this helps.
Rémy.
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.
Regards
Hylton
--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Context
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.