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.

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.

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

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.

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

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

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