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

Den sön 30 maj 2021 kl 18:05 skrev Hylton Conacher (ZR1HPC) <
hylton@conacher.co.za>:

> On 2021/05/29 23:36, Johnny Rosenberg wrote:
> > Den lör 29 maj 2021 kl 21:58 skrev Hylton Conacher (ZR1HPC) <
> > hylton@conacher.co.za>:
> >
> > > Hi Johnny,
> > >
> > > On 2021/05/28 23:44, Johnny Rosenberg wrote:
> > > > 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 to
> > > > > use as well as the syntax for that formula.
> > > > >
> > > > > I am aware of the availability of vlookup, hlookup, Index/Match
> formula
> > > > > and 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 a
> > > > > blank column between each year. What I want to calculate is the date
> the
> > > > > Max rain occurred. I am OK with the formula to obtain the MAX but I
> need
> > > > > help in constructing a formula to get the corresponding date.
> > > > >
> > > > > I had though the best would be Index and Match but no matter how I
> enter
> > > > > it 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
> date
> > > > > the originated.
> > > > >
> > > > > =INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first
> date
> > > > > under Highest Monthly
> > > >
> > > > Strange. I give a completely different result, so obviously my
> > > spreadsheet
> > > > isn'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 what
> > > > > ranges to insert into the Index(Match()) formula with ZERO success.
> > > > >
> > > > > I am manually entering the dates under each year when the max value is
> > > > > revealed by my formula. Would love to have it automated but my entire
> > > > > spreadsheet covers over 400 rows and more than 52 columns resulting in
> > > > > 26 tabs of graphs from the Data sheet.
> > > > >
> > > > > Is my data in the wrong order i.e. should the rainfall value column be
> > > > > before 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
> terms
> > > > > i.e. Reference, Row, Column, Range, Search Criterion, Lookup array.
> > > > >
> > > > > 2020                    2021                    Highest Monthly
> > > > > Date    2020 Rain       Date    2021 Rain       Date            Rain
> > > > > 01/19   9,5             01/15   3               2020/01/19      9,5
> > > > > 02/16   1,5             02/14   3,5             2021/02/14      3,5
> > > > > 03/25   3,5             03/14   19              2021/03/14      19
> > > > > 04/11   20              04/26   7               2020/04/11      20
> > > > > 05/28   27,5            05/20   43              2021/05/20      43
> > > > > 06/11   26              #N/A    0                               0
> > > > > 07/09   85,5            #N/A    0                               0
> > > > > 08/28   35              #N/A    0                               0
> > > > > 09/02   21              #N/A    0                               0
> > > > > 10/28   15              #N/A    0                               0
> > > > > 11/06   25              #N/A    0                               0
> > > > > 12/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 newer
> > > > > versions on LO.
> > > > >
> > > > > Regards
> > > > > Hylton
> > > > I'm not sure what you are trying to do here, so I'll just guess. Just
> > > > ignore me if I'm totally wrong.
> > > > So first, I tried to create a spreadsheet following the text above, so
> > > this
> > > > is 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
> assume
> > > > mm, since that's what we use where I live, and it doesn't matter for
> this
> > > > question 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 the
> > > > respective year with the highest amount of rain, and it's also the
> column
> > > > that 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
> > > the
> > > > B and E column per row, right? Column H  is already figured out, so for
> > > > instance, H3 contains the following:
> > > > =MAX(B3;E3)
> > > >
> > > > Then you want to automatically display the datum of which this occured,
> > > or
> > > > just the year? Well, date or year is only a question about formatting,
> so
> > > > let's just leave it to be formatted later. I guess you know how to do
> > > cell
> > > > formats and styles anyway.
> > > > maybe I totally misunderstood the question, but if not, you don't need
> > > > neither INDEX nor MATCH for this. Here's my cell formula in G3, for
> > > > instance:
> > > > =IF(B3>E3;A3;D3)     <————————————————————
> > > > So it there was more rain in 2020 than in 2021, display the date in
> > > column
> > > > A, otherwise display the date in column D.
> > > > If you want to fill further down to future dates and only show the
> values
> > > > when 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 display
> > > > something 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 think
> > > > there's a reason to use that in this case. I might have misunderstood
> the
> > > > whole question, though. Did I?
> > >
> > > You hit the nail on the head in terms of formatting and you are correct,
> > > I want to find the date values of the amounts listed in column H,
> > > ideally with the column heading too to show yyyy, but I could change the
> > > date format in A3:A15 and D3:d15 to include yyyy/mm/dd.
> > >
> > > Thanks for also showing me the "" trick instead of using an IF statement
> > > and having to include a 0 if the criteria are not met.
> > >
> > > So how would you calculate the date value in column G and be verbose
> > > with the explanation of the formula variables.
> >
> > I'm not sure I understand again. I already calculated it above, didn't I?
> > For instance, in cell G3:
> > =IF(B3>E3;A3;D3)
> > If the value in the B column (amount of rain for 2020) is greater than
> the
> > value in the E column (amount of rain for 2021), then show the date from
> > the A column, that is cell A3, else show the date from the D column, in
> > this case cell D3. Then format the G column to show what you want to see.
> > If only the year, then ”YYYY”, for instance. Isn't this what you are
> > looking for?
>
> Your answer almost works in that it does return the correct MM/DD
> PROVIDED there are only two values being compared. I currently have 6
> years of data to compare and once I have found the MAX thereof, out of
> interest I would like the MM/DD preceded by the YYYY from the column label.

Ok, maybe I missed that part. Then the INDEX thing is better, of course.
If 6 years, you'll end up in column S for the years and T for the rain, so
the formula will be:
=INDEX($A3:Q3;1;MATCH(T3;$A3:Q3;0)-1)

Note the $ signs which makes it possible to add more columns with a minimum
of hazzle. I also modified the Rain column like so:
=MAXIFS($B3:Q3;$B3:Q3;"<1000")
Note the $ signs and the use of the MAXIFS function which in this case only
cares about values lower than 1000 to filter the date columns out (unless
you want to include dates before 1902-09-27, then this won't work).

So what now, if you want to add another year?
Follow these steps and I think you'll be fine:

   - Highlight the two last columns, in this case S and T.
   - Copy them to three columns further to the right, in this case V and W.
   - Delete the old content of columns S and T.
   - Start entering data for the new year in columns S and T.


This should be easy to maintain without using any fancy stuff, I think.


Kind regards

Johnny Rosenberg




> I will send you a link to the spreadsheet namely
> <
> https://drive.google.com/file/d/1Asm-Jue2ITTswmt8Q4hpbuFK0EJV4oa8/view?usp=sharing
> >

Access denied. I sent a request for access that you might want to approve.
:)


> Look for the Data sheet and line A411. Anyone else can view too, for a
> while. I will be trying Remy's solution in the meantime as I think,
> given the number of years the data spans the Index/Match is a better
> fit. we shall see.
>
> 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

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