Calc Formula confusion an understanding needed Index/Match

Hylton_Conacher_ZR1H · May 28, 2021, 8:47pm

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

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

Johnny_Rosenberg · May 28, 2021, 9:44pm

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?

Kind regards

Johnny Rosenberg

Remy_Gauthier · May 29, 2021, 6:40pm

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.

Hylton_Conacher_ZR1H · May 29, 2021, 7:56pm

Hi Johnny,

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

Johnny_Rosenberg · May 29, 2021, 9:36pm

Hi Johnny,

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

Kind regards

Johnny Rosenberg

Hylton_Conacher_ZR1H · May 30, 2021, 4:03pm

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.

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

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

Hylton_Conacher_ZR1H · May 30, 2021, 8:11pm

Bravo Remy,

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

Johnny_Rosenberg · May 30, 2021, 8:23pm

> hylton@conacher.co.za>:
>
>> Hi Johnny,
>>
>>> 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.