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

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.

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