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
touse as well as the syntax for that formula.
I am aware of the availability of vlookup, hlookup, Index/Match
formulaand 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
ablank column between each year. What I want to calculate is the
date theMax rain occurred. I am OK with the formula to obtain the
MAX but I needhelp in constructing a formula to get the
corresponding date.
I had though the best would be Index and Match but no matter how I
enterit 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 datethe originated.
=INDEX(a1:h14;MATCH(h3;a3:h3;0)) just gives me 01/01 for the first
dateunder Highest Monthly
Strange. I give a completely different result, so obviously my
spreadsheetisn'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
whatranges to insert into the Index(Match()) formula with ZERO
success.
I am manually entering the dates under each year when the max value
isrevealed by my formula. Would love to have it automated but my
entirespreadsheet covers over 400 rows and more than 52 columns
resulting in26 tabs of graphs from the Data sheet.
Is my data in the wrong order i.e. should the rainfall value column
bebefore 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
termsi.e. Reference, Row, Column, Range, Search Criterion, Lookup
array.
2020 2021 Highest
MonthlyDate 2020 Rain Date 2021
Rain Date Rain01/19 9,5 01/15 3
2020/01/19 9,502/16 1,5 02/14 3,5
2021/02/14 3,503/25 3,5 03/14 19
2021/03/14 1904/11 20 04/26 7
2020/04/11 2005/28 27,5 05/20 43
2021/05/20 4306/11 26 #N/A 0
007/09 85,5 #N/A 0
008/28 35 #N/A 0
009/02 21 #N/A 0
010/28 15 #N/A 0
011/06 25 #N/A 0
012/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
newerversions on LO.
RegardsHylton
I'm not sure what you are trying to do here, so I'll just guess.
Justignore me if I'm totally wrong.So first, I tried to create a
spreadsheet following the text above, so thisis 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 assumemm, since that's what we use where I live,
and it doesn't matter for thisquestion 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 therespective year with
the highest amount of rain, and it's also the columnthat 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 theB and E column per row, right? Column H is already figured
out, so forinstance, H3 contains the following:=MAX(B3;E3)
Then you want to automatically display the datum of which this
occured, orjust the year? Well, date or year is only a question about
formatting, solet's just leave it to be formatted later. I guess you
know how to do cellformats and styles anyway.maybe I totally
misunderstood the question, but if not, you don't needneither INDEX
nor MATCH for this. Here's my cell formula in G3,
forinstance:=IF(B3>E3;A3;D3)So it there was more rain in 2020 than in
2021, display the date in columnA, otherwise display the date in
column D.If you want to fill further down to future dates and only
show the valueswhen 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
displaysomething 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
thinkthere's a reason to use that in this case. I might have
misunderstood thewhole question, though. Did I?
Kind regards
Johnny Rosenberg
--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
Privacy Policy |
Impressum (Legal Info) |
Copyright information: 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.