2021 Archives by date, by thread · List index

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

```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 searchThis 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 countThe 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.

Le vendredi 28 mai 2021 à 23:44 +0200, Johnny Rosenberg a écrit :
```
```Den fre 28 maj 2021 kl 22:49 skrev Hylton Conacher (ZR1HPC) <
hylton@conacher.co.za>:
```
```Hi,
I have LO 7.0.6.2 and am battling with understanding which formula
touse as well as the syntax for that formula.
I am aware of the availability of vlookup, hlookup, Index/Match
formulaand have settled I think on the right one i.e. Index/Match
Below is a portion of my spreadsheet that is divided as below with
ablank column between each year. What I want to calculate is the
date theMax rain occurred. I am OK with the formula to obtain the
MAX but I needhelp in constructing a formula to get the
corresponding date.
I had though the best would be Index and Match but no matter how I
enterit I cannot get the date listed under the Date column of 2020
or 2021,never mind actually retrieving the year from the same
column as the datethe originated.
=INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first
dateunder Highest Monthly
```
```
Strange. I give a completely different result, so obviously my
spreadsheetisn't identical to yours.In which cell did you put this
formula and what result did you expect?
```
```I have looked aver tutorial and their mothers trying to find out
whatranges to insert into the Index(Match()) formula with ZERO
success.
I am manually entering the dates under each year when the max value
isrevealed by my formula. Would love to have it automated but my
entirespreadsheet covers over 400 rows and more than 52 columns
resulting in26 tabs of graphs from the Data sheet.
Is my data in the wrong order i.e. should the rainfall value column
bebefore the Date it occurred?
I do not understand what ranges need to consist of when using
Index/Match.
Can someone point me to a decent tutorial explaining the different
termsi.e. Reference, Row, Column, Range, Search Criterion, Lookup
array.
2020                    2021                    Highest
MonthlyDate    2020 Rain       Date    2021
Rain       Date            Rain01/19   9,5             01/15   3
2020/01/19      9,502/16   1,5             02/14   3,5
2021/02/14      3,503/25   3,5             03/14   19
2021/03/14      1904/11   20              04/26   7
2020/04/11      2005/28   27,5            05/20   43
2021/05/20      4306/11   26              #N/A    0
007/09   85,5            #N/A    0
008/28   35              #N/A    0
009/02   21              #N/A    0
010/28   15              #N/A    0
011/06   25              #N/A    0
012/26   2               #N/A    0
0
If you want the entire spreadsheet it is available on direct
request,but ultimately I would like to understand how it works.
This will at least enable to use the formula successfully on
RegardsHylton

```
```I'm not sure what you are trying to do here, so I'll just guess.
Justignore me if I'm totally wrong.So first, I tried to create a
looks like:Row 1 and two are just headers.Column A is dates for 2020
formatted as Month/Day.Column B is amount of rain in some unit, not
sure which one, so I assumemm, since that's what we use where I live,
and it doesn't matter for thisquestion anyway.Column C is
empty.Column D is dates for 2021 formatted as Month/Day.Column E is
the amount of rain for 2021.Column F is empty.Column G is the column
that contains the dates for each month for therespective year with
the highest amount of rain, and it's also the columnthat you wish to
automate, is that right?Column H is the highest value of rain in
columns B and E for each row.
Right so far?
If so, you want column G to display the date for each maximum value
in theB and E column per row, right? Column H  is already figured
out, so forinstance, H3 contains the following:=MAX(B3;E3)
Then you want to automatically display the datum of which this
occured, orjust the year? Well, date or year is only a question about
formatting, solet's just leave it to be formatted later. I guess you
know how to do cellformats and styles anyway.maybe I totally
misunderstood the question, but if not, you don't needneither INDEX
nor MATCH for this. Here's my cell formula in G3,
forinstance:=IF(B3>E3;A3;D3)So it there was more rain in 2020 than in
2021, display the date in columnA, otherwise display the date in
column D.If you want to fill further down to future dates and only
show the valueswhen the rest of the row is completely entered, you
this:=IF(OR(A3="";B3="";D3="";E3="");"";IF(B3>E3;A3;D3))Then you
could just fill down and only the relevant cells will
displaysomething and the rest will be blank.You can do the same thing
to column H if you like, for instance in
H3:=IF(OR(A3="";B3="";D3="";E3="");"";MAX(B3;E3))
Sorry that this doesn't explain the MATCH() function, but I don't
thinkthere's a reason to use that in this case. I might have
misunderstood thewhole question, though. Did I?

Kind regards
Johnny Rosenberg

```
```--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/

```
```
--
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/