Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Percentage of a "sum of" amount in a different field of a pivot re | Excel Worksheet Functions | |||
Insert Calculated Field (wrong Qty*Price = wrong Amount) | Excel Discussion (Misc queries) | |||
Limit the amount of digits displayed in a field in Excel or Access | Excel Discussion (Misc queries) | |||
How can I calculate amount of time left based on amount spent? | Excel Worksheet Functions | |||
Sum a $ amount in part of a field | Excel Discussion (Misc queries) |