Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default help with formula

Hi there. I have my checkbook register in Excel and I'd like to add a column
that shows "actual balance", based on items that have cleared my account.
Currently, column G has the following formula to calcuate the balance:
=IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7)

When an item clears my account, I put a capital C in column D. Can the
above formula be altered so that if there is a C in column D, then it goes
ahead and performs the calculation. If there is no C, it does no calculation
and simply repeats the value in the cell above?

Ultimately, I'd like to have 2 columns, one would be a projected balance
(assuming everything clears my account) and one would be an actual balance
(to compare against the balance showing on my checking account). If anyone
can think of a different way to accomplish this, I'm open to ideas. :)

Thanks for your help!!
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 964
Default help with formula

For your "Projected" amount (column G) use this formula, starting in cell G3
and copy down.

=E3-F3+OFFSET(G3,-1,0)

For your "Actual" amount (column H) use this formula, starting in cell H3
and copy down.

=OFFSET(H3,-1,0)+IF(D3="C",E3-F3,0)

If your amounts start on a different row than 3, then adjust the cell
references accordingly. The use of the OFFSET function will allow you to
insert and delete rows later as needed without messing up your formulas.

HTH,
Elkar


"Treesy" wrote:

Hi there. I have my checkbook register in Excel and I'd like to add a column
that shows "actual balance", based on items that have cleared my account.
Currently, column G has the following formula to calcuate the balance:
=IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7)

When an item clears my account, I put a capital C in column D. Can the
above formula be altered so that if there is a C in column D, then it goes
ahead and performs the calculation. If there is no C, it does no calculation
and simply repeats the value in the cell above?

Ultimately, I'd like to have 2 columns, one would be a projected balance
(assuming everything clears my account) and one would be an actual balance
(to compare against the balance showing on my checking account). If anyone
can think of a different way to accomplish this, I'm open to ideas. :)

Thanks for your help!!

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default help with formula

Try this in G7:

=IF(D7="C",IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7),G6)

and copy down as necessary.

I'm not sure why you are checking for E7 and F7 being blank and
returning a blank if they are - wouldn't you want G6 returned in this
case?

Hope this helps.

Pete

Treesy wrote:

Hi there. I have my checkbook register in Excel and I'd like to add a column
that shows "actual balance", based on items that have cleared my account.
Currently, column G has the following formula to calcuate the balance:
=IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7)

When an item clears my account, I put a capital C in column D. Can the
above formula be altered so that if there is a C in column D, then it goes
ahead and performs the calculation. If there is no C, it does no calculation
and simply repeats the value in the cell above?

Ultimately, I'd like to have 2 columns, one would be a projected balance
(assuming everything clears my account) and one would be an actual balance
(to compare against the balance showing on my checking account). If anyone
can think of a different way to accomplish this, I'm open to ideas. :)

Thanks for your help!!


  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default help with formula

Thanks for the suggestion, I will try it. As far as the formula I'm using,
it from a template I downloaded from the Microsoft site. I think what it is
doing is preventing a running total going down my column, which I like. If
I've entered a transaction, then go ahead and do the total. Otherwise, I'd
rather not have a long column of my last calculation?

But thanks again for the formula, I'll give it a go. :)

"Pete_UK" wrote:

Try this in G7:

=IF(D7="C",IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7),G6)

and copy down as necessary.

I'm not sure why you are checking for E7 and F7 being blank and
returning a blank if they are - wouldn't you want G6 returned in this
case?

Hope this helps.

Pete

Treesy wrote:

Hi there. I have my checkbook register in Excel and I'd like to add a column
that shows "actual balance", based on items that have cleared my account.
Currently, column G has the following formula to calcuate the balance:
=IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7)

When an item clears my account, I put a capital C in column D. Can the
above formula be altered so that if there is a C in column D, then it goes
ahead and performs the calculation. If there is no C, it does no calculation
and simply repeats the value in the cell above?

Ultimately, I'd like to have 2 columns, one would be a projected balance
(assuming everything clears my account) and one would be an actual balance
(to compare against the balance showing on my checking account). If anyone
can think of a different way to accomplish this, I'm open to ideas. :)

Thanks for your help!!



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default help with formula

Hmmm...this seems to work but I did tweak the formula so it would subtract E3
from F3. Otherwise, it was giving me a negative number. However, I'd like
it to not show a running balance down the column if there aren't transactions
listed in the other columns (A through F). Can you help me add the "ISBLANK"
function to this formula? Otherwise, it works great! Thank you.

"Elkar" wrote:

For your "Projected" amount (column G) use this formula, starting in cell G3
and copy down.

=E3-F3+OFFSET(G3,-1,0)

For your "Actual" amount (column H) use this formula, starting in cell H3
and copy down.

=OFFSET(H3,-1,0)+IF(D3="C",E3-F3,0)

If your amounts start on a different row than 3, then adjust the cell
references accordingly. The use of the OFFSET function will allow you to
insert and delete rows later as needed without messing up your formulas.

HTH,
Elkar


"Treesy" wrote:

Hi there. I have my checkbook register in Excel and I'd like to add a column
that shows "actual balance", based on items that have cleared my account.
Currently, column G has the following formula to calcuate the balance:
=IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7)

When an item clears my account, I put a capital C in column D. Can the
above formula be altered so that if there is a C in column D, then it goes
ahead and performs the calculation. If there is no C, it does no calculation
and simply repeats the value in the cell above?

Ultimately, I'd like to have 2 columns, one would be a projected balance
(assuming everything clears my account) and one would be an actual balance
(to compare against the balance showing on my checking account). If anyone
can think of a different way to accomplish this, I'm open to ideas. :)

Thanks for your help!!



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default help with formula

Hi

Try
=IFCOUNT(E7:F7)<1,"",G6+F7-E7
and
=IFCOUNT(E7:F7)<1,"",G6+(F7-E7)*(D7="C")

--
Regards

Roger Govier


"Treesy" wrote in message
...
Hmmm...this seems to work but I did tweak the formula so it would
subtract E3
from F3. Otherwise, it was giving me a negative number. However, I'd
like
it to not show a running balance down the column if there aren't
transactions
listed in the other columns (A through F). Can you help me add the
"ISBLANK"
function to this formula? Otherwise, it works great! Thank you.

"Elkar" wrote:

For your "Projected" amount (column G) use this formula, starting in
cell G3
and copy down.

=E3-F3+OFFSET(G3,-1,0)

For your "Actual" amount (column H) use this formula, starting in
cell H3
and copy down.

=OFFSET(H3,-1,0)+IF(D3="C",E3-F3,0)

If your amounts start on a different row than 3, then adjust the cell
references accordingly. The use of the OFFSET function will allow
you to
insert and delete rows later as needed without messing up your
formulas.

HTH,
Elkar


"Treesy" wrote:

Hi there. I have my checkbook register in Excel and I'd like to
add a column
that shows "actual balance", based on items that have cleared my
account.
Currently, column G has the following formula to calcuate the
balance:
=IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7)

When an item clears my account, I put a capital C in column D. Can
the
above formula be altered so that if there is a C in column D, then
it goes
ahead and performs the calculation. If there is no C, it does no
calculation
and simply repeats the value in the cell above?

Ultimately, I'd like to have 2 columns, one would be a projected
balance
(assuming everything clears my account) and one would be an actual
balance
(to compare against the balance showing on my checking account).
If anyone
can think of a different way to accomplish this, I'm open to ideas.
:)

Thanks for your help!!



  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default help with formula

Doesn't like the formula. Gives an error. Can't figure out why. The cell
references are correct.

"Roger Govier" wrote:

Hi

Try
=IFCOUNT(E7:F7)<1,"",G6+F7-E7
and
=IFCOUNT(E7:F7)<1,"",G6+(F7-E7)*(D7="C")

--
Regards

Roger Govier


"Treesy" wrote in message
...
Hmmm...this seems to work but I did tweak the formula so it would
subtract E3
from F3. Otherwise, it was giving me a negative number. However, I'd
like
it to not show a running balance down the column if there aren't
transactions
listed in the other columns (A through F). Can you help me add the
"ISBLANK"
function to this formula? Otherwise, it works great! Thank you.

"Elkar" wrote:

For your "Projected" amount (column G) use this formula, starting in
cell G3
and copy down.

=E3-F3+OFFSET(G3,-1,0)

For your "Actual" amount (column H) use this formula, starting in
cell H3
and copy down.

=OFFSET(H3,-1,0)+IF(D3="C",E3-F3,0)

If your amounts start on a different row than 3, then adjust the cell
references accordingly. The use of the OFFSET function will allow
you to
insert and delete rows later as needed without messing up your
formulas.

HTH,
Elkar


"Treesy" wrote:

Hi there. I have my checkbook register in Excel and I'd like to
add a column
that shows "actual balance", based on items that have cleared my
account.
Currently, column G has the following formula to calcuate the
balance:
=IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7)

When an item clears my account, I put a capital C in column D. Can
the
above formula be altered so that if there is a C in column D, then
it goes
ahead and performs the calculation. If there is no C, it does no
calculation
and simply repeats the value in the cell above?

Ultimately, I'd like to have 2 columns, one would be a projected
balance
(assuming everything clears my account) and one would be an actual
balance
(to compare against the balance showing on my checking account).
If anyone
can think of a different way to accomplish this, I'm open to ideas.
:)

Thanks for your help!!




  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default help with formula

This formula didn't work and I'm not sure why. It is giving me an error but
the cell references are correct. The offset formula that Elkar provided is
working so I'm wondering if there is a way we can simpy incorporate the
ISBLANK function into the formula?

Thanks for the help.

"Roger Govier" wrote:

Hi

Try
=IFCOUNT(E7:F7)<1,"",G6+F7-E7
and
=IFCOUNT(E7:F7)<1,"",G6+(F7-E7)*(D7="C")

--
Regards

Roger Govier


"Treesy" wrote in message
...
Hmmm...this seems to work but I did tweak the formula so it would
subtract E3
from F3. Otherwise, it was giving me a negative number. However, I'd
like
it to not show a running balance down the column if there aren't
transactions
listed in the other columns (A through F). Can you help me add the
"ISBLANK"
function to this formula? Otherwise, it works great! Thank you.

"Elkar" wrote:

For your "Projected" amount (column G) use this formula, starting in
cell G3
and copy down.

=E3-F3+OFFSET(G3,-1,0)

For your "Actual" amount (column H) use this formula, starting in
cell H3
and copy down.

=OFFSET(H3,-1,0)+IF(D3="C",E3-F3,0)

If your amounts start on a different row than 3, then adjust the cell
references accordingly. The use of the OFFSET function will allow
you to
insert and delete rows later as needed without messing up your
formulas.

HTH,
Elkar


"Treesy" wrote:

Hi there. I have my checkbook register in Excel and I'd like to
add a column
that shows "actual balance", based on items that have cleared my
account.
Currently, column G has the following formula to calcuate the
balance:
=IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7)

When an item clears my account, I put a capital C in column D. Can
the
above formula be altered so that if there is a C in column D, then
it goes
ahead and performs the calculation. If there is no C, it does no
calculation
and simply repeats the value in the cell above?

Ultimately, I'd like to have 2 columns, one would be a projected
balance
(assuming everything clears my account) and one would be an actual
balance
(to compare against the balance showing on my checking account).
If anyone
can think of a different way to accomplish this, I'm open to ideas.
:)

Thanks for your help!!




  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default help with formula

Maybe..

=IF(COUNT(E7:F7)<1,"",G6+F7-E7)
and
=IF(COUNT(E7:F7)<1,"",G6+(F7-E7)*(D7="C"))



Treesy wrote:

Doesn't like the formula. Gives an error. Can't figure out why. The cell
references are correct.

"Roger Govier" wrote:

Hi

Try
=IFCOUNT(E7:F7)<1,"",G6+F7-E7
and
=IFCOUNT(E7:F7)<1,"",G6+(F7-E7)*(D7="C")

--
Regards

Roger Govier


"Treesy" wrote in message
...
Hmmm...this seems to work but I did tweak the formula so it would
subtract E3
from F3. Otherwise, it was giving me a negative number. However, I'd
like
it to not show a running balance down the column if there aren't
transactions
listed in the other columns (A through F). Can you help me add the
"ISBLANK"
function to this formula? Otherwise, it works great! Thank you.

"Elkar" wrote:

For your "Projected" amount (column G) use this formula, starting in
cell G3
and copy down.

=E3-F3+OFFSET(G3,-1,0)

For your "Actual" amount (column H) use this formula, starting in
cell H3
and copy down.

=OFFSET(H3,-1,0)+IF(D3="C",E3-F3,0)

If your amounts start on a different row than 3, then adjust the cell
references accordingly. The use of the OFFSET function will allow
you to
insert and delete rows later as needed without messing up your
formulas.

HTH,
Elkar


"Treesy" wrote:

Hi there. I have my checkbook register in Excel and I'd like to
add a column
that shows "actual balance", based on items that have cleared my
account.
Currently, column G has the following formula to calcuate the
balance:
=IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7)

When an item clears my account, I put a capital C in column D. Can
the
above formula be altered so that if there is a C in column D, then
it goes
ahead and performs the calculation. If there is no C, it does no
calculation
and simply repeats the value in the cell above?

Ultimately, I'd like to have 2 columns, one would be a projected
balance
(assuming everything clears my account) and one would be an actual
balance
(to compare against the balance showing on my checking account).
If anyone
can think of a different way to accomplish this, I'm open to ideas.
:)

Thanks for your help!!





--

Dave Peterson
  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default help with formula

Not following the thread, but the syntax is incorrect in the last two formulas
Roger posted.

Try these............

=IF(COUNT(E7:F7)<1,"",G6+F7-E7)

=IF(COUNT(E7:F7)<1,"",G6+(F7-E7)*(D7="C"))


Gord Dibben MS Excel MVP


On Mon, 8 Jan 2007 11:42:00 -0800, Treesy
wrote:

Doesn't like the formula. Gives an error. Can't figure out why. The cell
references are correct.

"Roger Govier" wrote:

Hi

Try
=IFCOUNT(E7:F7)<1,"",G6+F7-E7
and
=IFCOUNT(E7:F7)<1,"",G6+(F7-E7)*(D7="C")

--
Regards

Roger Govier


"Treesy" wrote in message
...
Hmmm...this seems to work but I did tweak the formula so it would
subtract E3
from F3. Otherwise, it was giving me a negative number. However, I'd
like
it to not show a running balance down the column if there aren't
transactions
listed in the other columns (A through F). Can you help me add the
"ISBLANK"
function to this formula? Otherwise, it works great! Thank you.

"Elkar" wrote:

For your "Projected" amount (column G) use this formula, starting in
cell G3
and copy down.

=E3-F3+OFFSET(G3,-1,0)

For your "Actual" amount (column H) use this formula, starting in
cell H3
and copy down.

=OFFSET(H3,-1,0)+IF(D3="C",E3-F3,0)

If your amounts start on a different row than 3, then adjust the cell
references accordingly. The use of the OFFSET function will allow
you to
insert and delete rows later as needed without messing up your
formulas.

HTH,
Elkar


"Treesy" wrote:

Hi there. I have my checkbook register in Excel and I'd like to
add a column
that shows "actual balance", based on items that have cleared my
account.
Currently, column G has the following formula to calcuate the
balance:
=IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7)

When an item clears my account, I put a capital C in column D. Can
the
above formula be altered so that if there is a C in column D, then
it goes
ahead and performs the calculation. If there is no C, it does no
calculation
and simply repeats the value in the cell above?

Ultimately, I'd like to have 2 columns, one would be a projected
balance
(assuming everything clears my account) and one would be an actual
balance
(to compare against the balance showing on my checking account).
If anyone
can think of a different way to accomplish this, I'm open to ideas.
:)

Thanks for your help!!







  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,886
Default help with formula

Hi
Sorry about that. I don't know how I managed to miss out the parentheses
for the IF formula, but I did.
Dave and Gord corrected it for you and all should now be working.

--
Regards

Roger Govier


"Treesy" wrote in message
...
Doesn't like the formula. Gives an error. Can't figure out why. The
cell
references are correct.

"Roger Govier" wrote:

Hi

Try
=IFCOUNT(E7:F7)<1,"",G6+F7-E7
and
=IFCOUNT(E7:F7)<1,"",G6+(F7-E7)*(D7="C")

--
Regards

Roger Govier


"Treesy" wrote in message
...
Hmmm...this seems to work but I did tweak the formula so it would
subtract E3
from F3. Otherwise, it was giving me a negative number. However,
I'd
like
it to not show a running balance down the column if there aren't
transactions
listed in the other columns (A through F). Can you help me add the
"ISBLANK"
function to this formula? Otherwise, it works great! Thank you.

"Elkar" wrote:

For your "Projected" amount (column G) use this formula, starting
in
cell G3
and copy down.

=E3-F3+OFFSET(G3,-1,0)

For your "Actual" amount (column H) use this formula, starting in
cell H3
and copy down.

=OFFSET(H3,-1,0)+IF(D3="C",E3-F3,0)

If your amounts start on a different row than 3, then adjust the
cell
references accordingly. The use of the OFFSET function will allow
you to
insert and delete rows later as needed without messing up your
formulas.

HTH,
Elkar


"Treesy" wrote:

Hi there. I have my checkbook register in Excel and I'd like to
add a column
that shows "actual balance", based on items that have cleared my
account.
Currently, column G has the following formula to calcuate the
balance:
=IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7)

When an item clears my account, I put a capital C in column D.
Can
the
above formula be altered so that if there is a C in column D,
then
it goes
ahead and performs the calculation. If there is no C, it does
no
calculation
and simply repeats the value in the cell above?

Ultimately, I'd like to have 2 columns, one would be a projected
balance
(assuming everything clears my account) and one would be an
actual
balance
(to compare against the balance showing on my checking account).
If anyone
can think of a different way to accomplish this, I'm open to
ideas.
:)

Thanks for your help!!






  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default help with formula

Speaking as one who "misses" often, I can empathize but offer no cure<g

That's why we hang around here in herds.......for protection.


Gord

On Mon, 8 Jan 2007 22:03:07 -0000, "Roger Govier"
wrote:

Hi
Sorry about that. I don't know how I managed to miss out the parentheses
for the IF formula, but I did.
Dave and Gord corrected it for you and all should now be working.


  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 20
Default help with formula

Hi. Thanks. I thought there might be some paratheses missing but wasn't
entirely sure where they belonged. I think I got it working. Initially, the
actual balance was not calculating correctly but upon closer inspection, it
was using the previous PROJECTED balance (column G) instead of the previous
ACTUAL balance (column H). So I changed the 2nd formula to
=IF(COUNT(E7:F7)<1,"",H6+(F7-E7)*(D7="C")) and everything works perfectly.
Thank you to everyone for your help!!


"Roger Govier" wrote:

Hi
Sorry about that. I don't know how I managed to miss out the parentheses
for the IF formula, but I did.
Dave and Gord corrected it for you and all should now be working.

--
Regards

Roger Govier


"Treesy" wrote in message
...
Doesn't like the formula. Gives an error. Can't figure out why. The
cell
references are correct.

"Roger Govier" wrote:

Hi

Try
=IFCOUNT(E7:F7)<1,"",G6+F7-E7
and
=IFCOUNT(E7:F7)<1,"",G6+(F7-E7)*(D7="C")

--
Regards

Roger Govier


"Treesy" wrote in message
...
Hmmm...this seems to work but I did tweak the formula so it would
subtract E3
from F3. Otherwise, it was giving me a negative number. However,
I'd
like
it to not show a running balance down the column if there aren't
transactions
listed in the other columns (A through F). Can you help me add the
"ISBLANK"
function to this formula? Otherwise, it works great! Thank you.

"Elkar" wrote:

For your "Projected" amount (column G) use this formula, starting
in
cell G3
and copy down.

=E3-F3+OFFSET(G3,-1,0)

For your "Actual" amount (column H) use this formula, starting in
cell H3
and copy down.

=OFFSET(H3,-1,0)+IF(D3="C",E3-F3,0)

If your amounts start on a different row than 3, then adjust the
cell
references accordingly. The use of the OFFSET function will allow
you to
insert and delete rows later as needed without messing up your
formulas.

HTH,
Elkar


"Treesy" wrote:

Hi there. I have my checkbook register in Excel and I'd like to
add a column
that shows "actual balance", based on items that have cleared my
account.
Currently, column G has the following formula to calcuate the
balance:
=IF(AND(ISBLANK(E7),ISBLANK(F7)),"",G6-E7+F7)

When an item clears my account, I put a capital C in column D.
Can
the
above formula be altered so that if there is a C in column D,
then
it goes
ahead and performs the calculation. If there is no C, it does
no
calculation
and simply repeats the value in the cell above?

Ultimately, I'd like to have 2 columns, one would be a projected
balance
(assuming everything clears my account) and one would be an
actual
balance
(to compare against the balance showing on my checking account).
If anyone
can think of a different way to accomplish this, I'm open to
ideas.
:)

Thanks for your help!!






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reusing formula Tony29 Excel Discussion (Misc queries) 7 September 7th 06 03:34 AM
Dynamic Range with unused formula messing up x axis on dynamic graph [email protected] Charts and Charting in Excel 2 February 2nd 06 09:02 PM
Match then lookup Tenacity Excel Worksheet Functions 9 December 3rd 05 06:30 AM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 06:18 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 03:28 AM


All times are GMT +1. The time now is 04:05 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"