Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculations in Excel
I am designing an invoice sheet and need to have formulas that calculate what
is due. My first problem is how to populate the balance column with the formula without the #Value showing until I actually have numbers that will populate that row. I tried the template for invoice but I need to modify it and then it does not work. Second, is how to you refer to the last populated balance row in a receipt. I cannot use the sum as then it adds all the balances. I just need the last balance row that has a number in it. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculations in Excel
Hi
for your first problem embedd your formula in an IF statement e.g. =IF(ISBLANK(A1),"",your_current_formula) to retrieve the last value in a column =LOOKUP(2,1/(-E1:E10<0),E1:E10) Where A1:A10 is the range you're interested in. Cheers JulieD "Luada" wrote in message ... I am designing an invoice sheet and need to have formulas that calculate what is due. My first problem is how to populate the balance column with the formula without the #Value showing until I actually have numbers that will populate that row. I tried the template for invoice but I need to modify it and then it does not work. Second, is how to you refer to the last populated balance row in a receipt. I cannot use the sum as then it adds all the balances. I just need the last balance row that has a number in it. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculations in Excel
On Sat, 19 Mar 2005 07:51:08 -0800, "Luada"
wrote: I am designing an invoice sheet and need to have formulas that calculate what is due. My first problem is how to populate the balance column with the formula without the #Value showing until I actually have numbers that will populate that row. I tried the template for invoice but I need to modify it and then it does not work. Second, is how to you refer to the last populated balance row in a receipt. I cannot use the sum as then it adds all the balances. I just need the last balance row that has a number in it. Two methods: 1. Use an IF statement to put something else there, like a null string: =IF(ISERROR(cell_ref),"",your_formula) 2. Use conditional formatting to test for the error, and, if present, make the font color = background color (default would be white). --ron |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculations in Excel
Thanks JulieD and Ron for your help. I have been retired for several years
and boy do you forget what use to be so easy to do. Okay, I was able to correct formulas, etc. and make Lookup work. I still have one problem. On the balance column, it keeps repeating the last balance all the way to the total. I only want the balance to be on the last entry line, regardless of how many other lines are below it. Example: Amount Charged Payment Received Balance 75.00 75.00 10.00 65.00 15.00 50.00 50.00 50.00 etc. I don't want the last two 50.00 to show as this is confusing to people. How do I mask the column until I actually enter a number on that row and perform the caluclation. "JulieD" wrote: Hi for your first problem embedd your formula in an IF statement e.g. =IF(ISBLANK(A1),"",your_current_formula) to retrieve the last value in a column =LOOKUP(2,1/(-E1:E10<0),E1:E10) Where A1:A10 is the range you're interested in. Cheers JulieD "Luada" wrote in message ... I am designing an invoice sheet and need to have formulas that calculate what is due. My first problem is how to populate the balance column with the formula without the #Value showing until I actually have numbers that will populate that row. I tried the template for invoice but I need to modify it and then it does not work. Second, is how to you refer to the last populated balance row in a receipt. I cannot use the sum as then it adds all the balances. I just need the last balance row that has a number in it. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculations in Excel
On Fri, 25 Mar 2005 15:47:02 -0800, "Luada"
wrote: Thanks JulieD and Ron for your help. I have been retired for several years and boy do you forget what use to be so easy to do. Okay, I was able to correct formulas, etc. and make Lookup work. I still have one problem. On the balance column, it keeps repeating the last balance all the way to the total. I only want the balance to be on the last entry line, regardless of how many other lines are below it. Example: Amount Charged Payment Received Balance 75.00 75.00 10.00 65.00 15.00 50.00 50.00 50.00 etc. I don't want the last two 50.00 to show as this is confusing to people. How do I mask the column until I actually enter a number on that row and perform the caluclation. What formula are you using? What happened when you tried JulieD's ISBLANK formula? --ron |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculations in Excel
Ron, I am using the following formula to get the balance:
=IF(F16=0,F16+H15-G16,"") where F is the payment column, H15 is the previous balance from the line above, and G is the payment column. "Ron Rosenfeld" wrote: On Fri, 25 Mar 2005 15:47:02 -0800, "Luada" wrote: Thanks JulieD and Ron for your help. I have been retired for several years and boy do you forget what use to be so easy to do. Okay, I was able to correct formulas, etc. and make Lookup work. I still have one problem. On the balance column, it keeps repeating the last balance all the way to the total. I only want the balance to be on the last entry line, regardless of how many other lines are below it. Example: Amount Charged Payment Received Balance 75.00 75.00 10.00 65.00 15.00 50.00 50.00 50.00 etc. I don't want the last two 50.00 to show as this is confusing to people. How do I mask the column until I actually enter a number on that row and perform the caluclation. What formula are you using? What happened when you tried JulieD's ISBLANK formula? --ron |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calculations in Excel
On Sat, 26 Mar 2005 12:51:04 -0800, "Luada"
wrote: Ron, I am using the following formula to get the balance: =IF(F16=0,F16+H15-G16,"") where F is the payment column, H15 is the previous balance from the line above, and G is the payment column. I'm not sure of how you enter data, but perhaps something like: =IF(AND(ISBLANK(Amt_charged),ISBLANK(Pmt)),"",IF(F 16=0,F16+H15-G16,"")) "Ron Rosenfeld" wrote: On Fri, 25 Mar 2005 15:47:02 -0800, "Luada" wrote: Thanks JulieD and Ron for your help. I have been retired for several years and boy do you forget what use to be so easy to do. Okay, I was able to correct formulas, etc. and make Lookup work. I still have one problem. On the balance column, it keeps repeating the last balance all the way to the total. I only want the balance to be on the last entry line, regardless of how many other lines are below it. Example: Amount Charged Payment Received Balance 75.00 75.00 10.00 65.00 15.00 50.00 50.00 50.00 etc. I don't want the last two 50.00 to show as this is confusing to people. How do I mask the column until I actually enter a number on that row and perform the caluclation. What formula are you using? What happened when you tried JulieD's ISBLANK formula? --ron --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with calculations in Excel | Excel Discussion (Misc queries) | |||
DEG MIN SEC CALCULATIONS IN EXCEL. | Excel Worksheet Functions | |||
calculations in excel | Excel Discussion (Misc queries) | |||
calculations in excel | Excel Worksheet Functions | |||
excel calculations | Excel Worksheet Functions |