What formula would I use?

L

Leo V.

I asked this question before, but didn't get any responses. So, I'll try to
be clearer this time.

Month Loan Amount
Jan 06 $3300.00
Feb 06 $3279.00
Mar 06 $3250.00
Apr 06
May 06
..
..
..
Dec 06

After this, there is a blank row, then I want a Year to date difference.

In order to calculate that, I need to search up from Dec 06 for the first
entry that isn't "".

YTD Diff = LastNonBlankLoanAmt - $3300.00 (B2)

I've tried formula's that I thought would work, but I keep getting VALUE? or
NAME? errors.

I'm using OpenOffice Calc 2.0, so a worksheet formula would be preferred
over a VBA function.
 
B

Biff

Hi!

These work in Excel. The examples that use Lookup rely on a "bug" in Excel.
Whether they'll work in OO, ???????
YTD Diff = LastNonBlankLoanAmt - $3300.00 (B2)
Click to expand...

Did you mean: $3300.00 (B2) - LastNonBlankLoanAmt

If the loan amount decreases every month:

=MAX(B2:B13)-MIN(B2:B13)

Otherwise, one of these:

=B2-LOOKUP(9.99999999999999E+307,B2:B13)

=B2-LOOKUP(MAX(B2:B13)+1,B2:B13)

Biff
 
L

Leo V.

Thanks for your response.

The loan is really a credit card balance, so it may go up or down.

I just want to see the total difference from January to the most recent
month entered (YTD difference).

So... I tried the last two calculations you provided. Using both ENTER and
CTRL-SHIFT-ENTER and adjusting for the real ranges on my sheet (Jan loan
amount is in B6 and I keep getting the same responses
I've been getting with other attempts:
#NAME?

Maybe OpenOffice can't handle LOOKUP or MAX? Sounds odd as I've loaded some
fairly complex excel spreadsheets. I know MAX works, I've used it
independently. I don't know what is up.
 
Top