I have LO 220.127.116.11 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
=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.
To unsubscribe e-mail to: email@example.com
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
- [libreoffice-users] Calc Formula confusion an understanding needed Index/Match · Hylton Conacher (ZR1HPC)
Impressum (Legal Info)
: Unless otherwise specified, all text and images
on this website are licensed under the
Creative Commons Attribution-Share Alike 3.0 License
This does not include the source code of LibreOffice, which is
licensed under the Mozilla Public License (MPLv2
"LibreOffice" and "The Document Foundation" are
registered trademarks of their corresponding registered owners or are
in actual use as trademarks in one or more countries. Their respective
logos and icons are also subject to international copyright laws. Use
thereof is explained in our trademark policy