Carrying last $$$ amount to another field
 

Hi... I'm working with a Billing Statement in Excel. Going down the colum is
the sum of charges and credits from the two columns from the left. The
billing statement i'm working with is rather standard. I'm simply trying to
take the balance on the last line (the sum of the above line charges/credits)
and place that dollar value in a "Current Balance Due" field at the bottom of
the statement. What is the named function I use to say, "take the last
current balance dollar value and put it in the Current Balance Due Field"
which I'm trying to create? Since the number of lines (charges/credits) in
billing statements varies, I need the formulae to know to look only to the
last line of any series to copy, not a fixed line.
--
Greatly appreciated!
Thanks
Gene

Carrying last $$$ amount to another field
 

You are not specifying which column or rows you are using. As such I cannot
be specific. You will therefore have to adapt the formula to suit your needs.

Let's say your balance rows are 10 - 24 in Col G.
Where you want the final balance insert

=OFFSET(G10,COUNT(G10:G24)-1,0)

The purpose being to count the non-empty rows, and read the offset row from
line 10, minus 1 row.

This obviously means that you should not have empty rows in between,
otherwise the formula would not work.

--
HTH

Kassie

Replace xxx with hotmail


"Challenged" wrote:

Hi... I'm working with a Billing Statement in Excel. Going down the colum is
the sum of charges and credits from the two columns from the left. The
billing statement i'm working with is rather standard. I'm simply trying to
take the balance on the last line (the sum of the above line charges/credits)
and place that dollar value in a "Current Balance Due" field at the bottom of
the statement. What is the named function I use to say, "take the last
current balance dollar value and put it in the Current Balance Due Field"
which I'm trying to create? Since the number of lines (charges/credits) in
billing statements varies, I need the formulae to know to look only to the
last line of any series to copy, not a fixed line.
--
Greatly appreciated!
Thanks
Gene

Carrying last $$$ amount to another field
 

Ok Kassie.... I'm close... How do I make the bottom right field ONLY show the
last $1,000 amout? And if a payment is made next month bringing the net due
BELOW $1,000 on the next line, how do I show the new net "Current Balance"???
Thanks! Gene
__________________________________________________ __________________
DATE DESCRIPTION CHARGES CREDITS ACCOUNT BALANCE
October 19, 2008 -- THE PURCHASE OF THE BOAT
Oct. 19, 2008 Purchase Price of Boat $3,000.00 $3,000.00
Oct. 19, 2008 Deposit Paid $1,000.00 $2,000.00
Dec. 5, 2008 Nov. 19th paymnet made $500.00 $1,500.00
Dec.31, 2008 Dec. 19th payment made $500.00 $1,000.00
Jan. 19, 2009 $500 PAYMENT DUE $1,000.00
Feb. 19, 2009 $500 PAYMENT DUE $1,000.00


Balance Due $0.00

--
Greatly appreciated!
Thanks
Gene


"Kassie" wrote:

You are not specifying which column or rows you are using. As such I cannot
be specific. You will therefore have to adapt the formula to suit your needs.

Let's say your balance rows are 10 - 24 in Col G.
Where you want the final balance insert

=OFFSET(G10,COUNT(G10:G24)-1,0)

The purpose being to count the non-empty rows, and read the offset row from
line 10, minus 1 row.

This obviously means that you should not have empty rows in between,
otherwise the formula would not work.

--
HTH

Kassie

Replace xxx with hotmail


"Challenged" wrote:

Hi... I'm working with a Billing Statement in Excel. Going down the colum is
the sum of charges and credits from the two columns from the left. The
billing statement i'm working with is rather standard. I'm simply trying to
take the balance on the last line (the sum of the above line charges/credits)
and place that dollar value in a "Current Balance Due" field at the bottom of
the statement. What is the named function I use to say, "take the last
current balance dollar value and put it in the Current Balance Due Field"
which I'm trying to create? Since the number of lines (charges/credits) in
billing statements varies, I need the formulae to know to look only to the
last line of any series to copy, not a fixed line.
--
Greatly appreciated!
Thanks
Gene

Carrying last $$$ amount to another field
 

Hi,

If you want to know the last numeric entry in range D4:D18, you may use
=LOOKUP(99^99,D4:D18)

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Challenged" wrote in message
...
Hi... I'm working with a Billing Statement in Excel. Going down the colum
is
the sum of charges and credits from the two columns from the left. The
billing statement i'm working with is rather standard. I'm simply trying
to
take the balance on the last line (the sum of the above line
charges/credits)
and place that dollar value in a "Current Balance Due" field at the bottom
of
the statement. What is the named function I use to say, "take the last
current balance dollar value and put it in the Current Balance Due Field"
which I'm trying to create? Since the number of lines (charges/credits)
in
billing statements varies, I need the formulae to know to look only to the
last line of any series to copy, not a fixed line.
--
Greatly appreciated!
Thanks
Gene

Carrying last $$$ amount to another field
 

OK, it seems you are working in Col E

Adapt the formula to work in Col E.
Something like =OFFSET(E2,COUNT(E2:E24)-1,0), and of course adjust the E24
to accomodate the last possible row where an amount may be shown.
Alternatively, as Ashish Mathur indicated, you can use
=LOOKUP(99^99,E2:E24), and again adjust the E24 to read the last possible
line.

--
HTH

Kassie

Replace xxx with hotmail


"Challenged" wrote:

Ok Kassie.... I'm close... How do I make the bottom right field ONLY show the
last $1,000 amout? And if a payment is made next month bringing the net due
BELOW $1,000 on the next line, how do I show the new net "Current Balance"???
Thanks! Gene
__________________________________________________ __________________
DATE DESCRIPTION CHARGES CREDITS ACCOUNT BALANCE
October 19, 2008 -- THE PURCHASE OF THE BOAT
Oct. 19, 2008 Purchase Price of Boat $3,000.00 $3,000.00
Oct. 19, 2008 Deposit Paid $1,000.00 $2,000.00
Dec. 5, 2008 Nov. 19th paymnet made $500.00 $1,500.00
Dec.31, 2008 Dec. 19th payment made $500.00 $1,000.00
Jan. 19, 2009 $500 PAYMENT DUE $1,000.00
Feb. 19, 2009 $500 PAYMENT DUE $1,000.00


Balance Due $0.00

--
Greatly appreciated!
Thanks
Gene


"Kassie" wrote:

You are not specifying which column or rows you are using. As such I cannot
be specific. You will therefore have to adapt the formula to suit your needs.

Let's say your balance rows are 10 - 24 in Col G.
Where you want the final balance insert

=OFFSET(G10,COUNT(G10:G24)-1,0)

The purpose being to count the non-empty rows, and read the offset row from
line 10, minus 1 row.

This obviously means that you should not have empty rows in between,
otherwise the formula would not work.

--
HTH

Kassie

Replace xxx with hotmail


"Challenged" wrote:

Hi... I'm working with a Billing Statement in Excel. Going down the colum is
the sum of charges and credits from the two columns from the left. The
billing statement i'm working with is rather standard. I'm simply trying to
take the balance on the last line (the sum of the above line charges/credits)
and place that dollar value in a "Current Balance Due" field at the bottom of
the statement. What is the named function I use to say, "take the last
current balance dollar value and put it in the Current Balance Due Field"
which I'm trying to create? Since the number of lines (charges/credits) in
billing statements varies, I need the formulae to know to look only to the
last line of any series to copy, not a fixed line.
--
Greatly appreciated!
Thanks
Gene