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
--
To unsubscribe e-mail to: users+unsubscr...@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
