6

I'm looking for a formula in LibreOffice Calc to find the last value in a range that meets a condition. Here is sample data:

A         B             C           D                E                 F
Date      US Amount     Currency    Local Amount     Exchange Rate     Reconciled
01/01/01  $1000.00      GBP         654 GBP          1.53              x
01/05/01  $1000.00      EUR         757.12 EUR       1.32              x
01/18/01  $1000.00      GBP         688 GBP          1.45              x
01/19/01  $1000.00      EUR         763.36 EUR       1.31              x
01/28/01  $1000.00      GBP         675.58 GBP       1.48
01/29/01  $1000.00      EUR         778.87 EUR       1.28

So, I'm looking for a formula that would pull the last, reconciled exchange rate of the appropriate currency. (In this case, for EUR it would pull 1.31, as the 1.28 hasn't been reconciled yet.) It seems that in Excel I might be able to use the DLAST function, however that doesn't seem to have been implemented in LibreOffice.

Any workarounds for LibreOffice?

Improve this question

1 Answer 1

Reset to default
4

Yes - you can use the MATCH function to find the last row matching a certain criteria. This way, you can calculate the OFFSET based on the first row. It would look like this:

=OFFSET(E2;(MATCH("x";F1:F100;-1)-1);0;1)

Or, with some breaks and comments inserted:

=OFFSET(              // find Offset ...
    E2;               // ... starting from first exchange rate ...
    (                 // ... and go down some rows:
        MATCH(        // get row of last reconciled rate using MATCH:
            "x";      // search for "x" ...
            F2:F100;  // in F1 to F100 (make sure that the range covers all rates!)
            -1        // assuming a descending sort order. So, the last of a sequence
                      // of identical values is the "first" one.
        )-1           // MATCH would return 4, so OFFSET would point to 5,
                      // so reduce by -1
    );
    0;                // no "horizontal" offset (stay in the same column)
    1)                // return only one cell
Improve this answer
3
  • 2
    Yes, based on the sample data that would work for the last reconciled Euro exchange rate, however that wouldn't work for the last reconciled Pound exchange rate. This would always give me the last reconciled exchange rate, regardless of what currency it is. I guess it's more that there are two conditions - one, whether the exchange rate has been "reconciled" or not, and two, which exchange rate it is. Does that make sense?
    – Allez les lions
    Sep 9, 2013 at 18:20
  • Ah, ok, i will try to integrate a lookup for the currency into the formula. You're completely right, my formula just checks for the last reconciled rate, independent of the currency.
    – tohuwawohu
    Sep 9, 2013 at 18:26
  • 1
    Ok, so I added in hidden columns for each currency that tracked for each currency when they were reconciled so I could use the MATCH formula you came up with accordingly. It's less than ideal, but it works and I would guess is likely the best option. Thanks.
    – Allez les lions
    Sep 10, 2013 at 13:19

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .