2022 Archives by date, by thread · List index

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

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

VinceB.

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