# LO 6.4.6.2 CALC : Appropriate formula? Classic List Threaded 5 messages Open this post in threaded view
|

## LO 6.4.6.2 CALC : Appropriate formula? HI, USING I have eight data values on a single row with corresponding dates next to each value in the format below i.e. A B C D E F G H 25 01/07 blank 60 09/05 blank 30 26/7 In the 2nd row I would like to show the highest number from the first row as well as the corresponding date value in the cell to the right of that value. My A2 formula is MAX(A2,D2) = 60 The issue I am having is to get the corresponding date value from the adjacent column i.e. 09/05 in row B2. The formula I have tried include: =VLOOKUP(MAX(A1,D1,G1),B1:H1,FALSE) = #N/A =MATCH(INDEX(A2),A1:H1,0) = #N/A =HLOOKUP(MAX(A1,D1,G1),A1:H1,1,0)  =60 BEING THE SAME VALUE AS MAX(). I figured that as VLOOKUP looks for values below that HLOOKUP might be better suited. That said I have always battled understanding the terminology for the requirements for both vlookup and hlookup, even Index and Match needs understanding I don't have. I read the LO manual online and Google has been some help but seems to always go the VLOOKUP route with all the data under each other. Unfortunately this sort of 'table' VLOOKUP requires is not feasible on my data set. I should probably use BASE but have not delved into that especially as the data making up these figures is graphed. Anyone's guidance appreciated. Regards Hylton mailto:[hidden email] P.S.: I am subscribed to the LO-Users mailing list -- To unsubscribe e-mail to: [hidden email] Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/Posting guidelines + more: https://wiki.documentfoundation.org/NetiquetteList archive: https://listarchives.libreoffice.org/global/users/Privacy Policy: https://www.documentfoundation.org/privacy
Open this post in threaded view
|

## Re: LO 6.4.6.2 CALC : Appropriate formula? At 13:50 15/09/2020 +0200, Hylton Conacher wrote: >I have eight data values on a single row with corresponding dates >next to each value in the format below i.e. > >A       B       C       D       E       F       G       H >25      01/07   blank   60      09/05   blank   30      26/7 > >In the 2nd row I would like to show the highest number from the >first row as well as the corresponding date value in the cell to the >right of that value. > >My A2 formula is MAX(A2,D2) = 60 Shouldn't that be =MAX(A1;D1;G1) ? >The issue I am having is to get the corresponding date value from >the adjacent column i.e. 09/05 in row B2. How about =OFFSET(A1;0;MATCH(A2;A1:G1;0)) ? Wouldn't you be better off placing your values in three rows of two columns? Then you could use VLOOKUP(). >Unfortunately this sort of 'table' VLOOKUP requires is not feasible >on my data set. I don't see why not. It might well be that reorganising your data in some way is the right solution. I trust this helps. Brian Barker -- To unsubscribe e-mail to: [hidden email] Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/Posting guidelines + more: https://wiki.documentfoundation.org/NetiquetteList archive: https://listarchives.libreoffice.org/global/users/Privacy Policy: https://www.documentfoundation.org/privacy
Open this post in threaded view
|

## Re: LO 6.4.6.2 CALC : Appropriate formula? In reply to this post by zr1hpc =If I understand your issue?? Don't know if this will come thru OK. Put these vaues in row 1 and formula in A2 and B2. Change Date to my format. Changed values in D1,A1, and G1, and got the matching date when it was larger. 25 07/01/20 60 05/09/20 30 07/26/20 60 05/09/20 A2: =IF(AND(A1D1,A1G1),A1,IF(AND(D1A1,D1=G1),D1,G1)) B2: =IF(AND(A1D1,A1G1),B1,IF(AND(D1A1,D1=G1),E1,H1)) A2: =IF(AND(A1D1,A1G1),A1,IF(AND(D1A1,D1G1),D1,G1)) B2: =IF(AND(A1D1,A1G1),B1,IF(AND(D1A1,D1G1),E1,H1)) On 15 Sep 2020 at 13:50, Hylton Conacher (ZR1HPC) wrote: Send reply to:[hidden email] From:60;Hylton Conacher (ZR1HPC)[hidden email] Subject:;[libreoffice-users] LO 6.4.6.2 CALC : Appropriate formula? To:;=LibreOffice [hidden email] sent:60;Tue, 15 Sep 2020 13:50:38 +0200 HI, USING I have eight data values on a single row with corresponding dates next to each value in the format below i.e. AB C&# -- To unsubscribe e-mail to: [hidden email] Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/Posting guidelines + more: https://wiki.documentfoundation.org/NetiquetteList archive: https://listarchives.libreoffice.org/global/users/Privacy Policy: https://www.documentfoundation.org/privacy In reply to this post by zr1hpc First attempt. List removed the formating and stripped the greater than signs?? So, manual turned off rich text, and see what this attempt looks like. If I understand your issue?? Don't know if this will come thru OK. Put these vaues in row 1 and formula in A2 and B2. Change Date to my format. Changed values in D1,A1, and G1, and got the matching date when it was larger.                                                                   25                                                             07/01/20                                                                   60                                                             05/09/20                                                                   30                                                             07/26/20                                                                                                                                       60                                                             05/09/20 "A2: =IF(AND(A1>D1,A1>G1),A1,IF(AND(D1>A1,D1>G1),D1,G1)) "B2: =IF(AND(A1>D1,A1>G1),B1,IF(AND(D1>A1,D1>G1),E1,H1)) "A2: =IF(AND(A1>D1,A1>G1),A1,IF(AND(D1>A1,D1>G1),D1,G1)) "B2: =IF(AND(A1>D1,A1>G1),B1,IF(AND(D1>A1,D1>G1),E1,H1)) On 15 Sep 2020 at 13:50, Hylton Conacher (ZR1HPC) wrote: Send reply to: [hidden email] From: "Hylton Conacher (ZR1HPC)" <[hidden email]> Subject: [libreoffice-users] LO 6.4.6.2 CALC : Appropriate formula? To: LibreOffice Users <[hidden email]> Date sent: Tue, 15 Sep 2020 13:50:38 +0200 > HI, > > USING > > I have eight data values on a single row with corresponding dates next > to each value in the format below i.e. > > ABCDEFGH > 2501/07blank6009/05blank3026/7 > > In the 2nd row I would like to show the highest number from the first > row as well as the corresponding date value in the cell to the right of > that value. > > My A2 formula is MAX(A2,D2) = 60 > > The issue I am having is to get the corresponding date value from the > adjacent column i.e. 09/05 in row B2. > > The formula I have tried include: > =VLOOKUP(MAX(A1,D1,G1),B1:H1,FALSE) = #N/A > =MATCH(INDEX(A2),A1:H1,0) = #N/A > =HLOOKUP(MAX(A1,D1,G1),A1:H1,1,0) =60 BEING THE SAME VALUE AS MAX(). > > I figured that as VLOOKUP looks for values below that HLOOKUP might be > better suited. That said I have always battled understanding the > terminology for the requirements for both vlookup and hlookup, even > Index and Match needs understanding I don't have. > > I read the LO manual online and Google has been some help but seems to > always go the VLOOKUP route with all the data under each other. > Unfortunately this sort of 'table' VLOOKUP requires is not feasible on > my data set. > > I should probably use BASE but have not delved into that especially as > the data making up these figures is graphed. > > Anyone's guidance appreciated. > > Regards > Hylton > mailto:[hidden email] > P.S.: I am subscribed to the LO-Users mailing list > > > -- > To unsubscribe e-mail to: [hidden email] > 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+------------------------------------------------------------+  Michael D. Setzer II - Computer Science Instructor (Retired)      mailto:[hidden email]                              mailto:[hidden email]  Guam - Where America's Day Begins                          G4L Disk Imaging Project maintainer  http://sourceforge.net/projects/g4l/+------------------------------------------------------------+ -- To unsubscribe e-mail to: [hidden email] Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/Posting guidelines + more: https://wiki.documentfoundation.org/NetiquetteList archive: https://listarchives.libreoffice.org/global/users/Privacy Policy: https://www.documentfoundation.org/privacy In reply to this post by zr1hpc Hello, An answer your V/HLOOKUP question: HLOOKUP searches for a value on a row (Horizontally) and then goes down to fetch the corresponding entry on a lower rowVLOOKUP searches for a value in a column (Vertically) and then goes across to fetch the corresponding entry on a column to the right In the case of your data, it seems to be placed in rows, so HLOOKUP would be the function to use, which is what your test shows. The way I would get the two entries you are looking for is: Maximum value: =MAXIFS(A1:H1,A1:H1,CONCAT("<";DATE(2000,1,1)))Corresponding date value: =INDIRECT(ADDRESS(LINE(A1),MATCH(maximum_value,A1:H1,0)+1)) I made the assumption your dates are on or after Jan 1, 2000 and that the values will be less than 36526 (equivalent date value of Jan 1, 2000): this allows me to find the max for the entire row at once without thinking about where the dates and values are. Then, I use MATCH to find where the value is, which feeds the INDIRECT/ADDRESS pair to extract the value. Note that this strategy will find the first maximum value if the values can be identical. If the date values are always text values, you could find the max on the row with this formula (replace -99999 with any small enough number):{=MAX(IF(ISNUMBER(A1:H1),A1:H1,-99999))} Notice the curly braces ('{" and "}"): this means it is an array formula and is entered by typing: =MAX(IF(ISNUMBER(A1:H1),A1:H1,-99999)) followed by CTRL-SHIFT-Enter I hope this helps. Rémy. Le mardi 15 septembre 2020 à 13:50 +0200, Hylton Conacher (ZR1HPC) a écrit : > HI, > USING > I have eight data values on a single row with corresponding dates > next to each value in the format below i.e. > A B C D E F G H25 01/07 blank > 60 09/05 blank 30 26/7 > In the 2nd row I would like to show the highest number from the first > row as well as the corresponding date value in the cell to the right > of that value. > My A2 formula is MAX(A2,D2) = 60 > The issue I am having is to get the corresponding date value from the > adjacent column i.e. 09/05 in row B2. > The formula I have tried include:=VLOOKUP(MAX(A1,D1,G1),B1:H1,FALSE) > = #N/A=MATCH(INDEX(A2),A1:H1,0) = > #N/A=HLOOKUP(MAX(A1,D1,G1),A1:H1,1,0)  =60 BEING THE SAME VALUE AS > MAX(). > I figured that as VLOOKUP looks for values below that HLOOKUP might > be better suited. That said I have always battled understanding the > terminology for the requirements for both vlookup and hlookup, even > Index and Match needs understanding I don't have. > I read the LO manual online and Google has been some help but seems > to always go the VLOOKUP route with all the data under each other. > Unfortunately this sort of 'table' VLOOKUP requires is not feasible > on my data set. > I should probably use BASE but have not delved into that especially > as the data making up these figures is graphed. > Anyone's guidance appreciated. > RegardsHyltonmailto:[hidden email] > P.S.: I am subscribed to the LO-Users mailing list > -- To unsubscribe e-mail to: [hidden email] Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/Posting guidelines + more: https://wiki.documentfoundation.org/NetiquetteList archive: https://listarchives.libreoffice.org/global/users/Privacy Policy: https://www.documentfoundation.org/privacy