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
> > newerversions on LO.
> > 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
> spreadsheet following the text above, so thisis what my spreadsheet
> 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
> could add another test,like
> 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/
> > Privacy Policy: https://www.documentfoundation.org/privacy

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