Re: [libreoffice-users] Calc Formula confusion an understanding needed Index/Match

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