[libreoffice-users] Re: Explanation of Formula that was used in Excel

On 10/30/2022 12:37 PM, Vince@Verizon wrote:
> Hi:
>
> Version: 7.3.2.2 (x64) / LibreOffice Community
> Build ID: 49f2b1bff42cfccbd8f788c8dc32c1c309559be0
> CPU threads: 8; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; 
> VCL: win
> Locale: en-US (en_US); UI: en-US
> Calc: CL
>
> I am attempting to understand a formula that was suggested for use 
> within MSExcel2016 so I might us it within LibreOffice Calc.
>
> The formula is:
>
> =INDEX(IF(INDEX(A:A,LARGE(IF($A$4:$B$43=$G$2,ROW($A$4:$A$43),""),ROW(1:1)))=$G$2,E:E,F:F),LARGE(IF($A$4:$B43=$G$2,ROW($A$4:$A$43),""),ROW(1:1))). 
>
>
> When I copy/paste this formula first into Notepad and then copy/paste 
> from Notepad into a LO Calc spreadsheet, a  #VALUE!  error is returned.
>
> I have not ever used e.g., INDEX(A:A) nor ROW(1:1) as a range 
> identifier/specifier within a formula, so I wonder if they are 
> acceptable within LO Calc, or is that causing the error,  #VALUE! ?  I 
> believe those identify/specify the entire [A] column and the entire 
> [1] row; is there an equivalent within LO Calc.
>
> Once the error is cleared then I will continue to attempt to 
> understand what the formula is calculating/doing within LO Calc. I can 
> see that it is evaluating the data within cell $G$2, the LARGEST value 
> within A:A, within E:E, and within F:F; but not much else is making 
> any sense for me to understand. A simplified example would be 
> appreciated here.
>
> Thanks for reading about my problem and for your comments/reply.
>
>
> VinceB.

Update:

Here is a link to the original MSExcel spreadsheet, the OP posted at the 
Excel Forum; the OP was seeking "Best formula to lookup most recent and 
second most recent result from list of data" on 01-04-2016, 02:50 AM:

https://www.excelforum.com/attachments/excel-general/438242d1451890188-best-formula-to-lookup-most-recent-and-second-most-recent-result-from-list-of-data-lookup-formula.xlsx

______________________________________________


Unfortunately, I do not understand the significance of:  ROW(1:1) within 
the original formula.

Error #519 says in part,

> The formula yields a value that does not correspond to the definition;
> Or a cell that is referenced in the formula contains text instead of a 
> number.
>
> No result (#VALUE is in the cell rather than Err:519!)


and it is true that columns H:J for row1 contain text strings, not 
numerical data.

I am still at a loss of understanding .








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