Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Personal Ledger Problem
Here's my problem:
I am keeping track of my Deposits and Withdrawls in Excel, so that I have up-to-date info on how much I have in my account. Currently I have it set up so that when a transaction occurs I hard code in date, description,withdrawl amt, or deposit amt and the the Balance Col contaitns the following formula: =IF(D32&E32="","",F31-D32+E32). The next objective I want to concur is that right now I have a check that has not been cashed by the recipient. My only balance is off by the amount of that check. As a check to make sure I have not missed any transactions I want to be able to input my online balance into a cell and then have the cell next to it automatically take the last balance number on my spreadsheet and tell me how much my balance off by. I was trying to do something with IF formulas using that if the balance cell below it is blank then use the cell above it, but I couldn't get it to work. Please help. Thanks, Steve |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Personal Ledger Problem
Hi,
I don't understand your formula, if D32&E32="","", what about the previous balance?, could you post an example of your sheet, maybe the solution is this, I suppose that your initial balance started in F1, then in G1 you enter the balance from the web, in H1 enter =g1-F1-sum($D$1:$D$1000)+sum($E$1:$E$1000) "Steve" wrote: Here's my problem: I am keeping track of my Deposits and Withdrawls in Excel, so that I have up-to-date info on how much I have in my account. Currently I have it set up so that when a transaction occurs I hard code in date, description,withdrawl amt, or deposit amt and the the Balance Col contaitns the following formula: =IF(D32&E32="","",F31-D32+E32). The next objective I want to concur is that right now I have a check that has not been cashed by the recipient. My only balance is off by the amount of that check. As a check to make sure I have not missed any transactions I want to be able to input my online balance into a cell and then have the cell next to it automatically take the last balance number on my spreadsheet and tell me how much my balance off by. I was trying to do something with IF formulas using that if the balance cell below it is blank then use the cell above it, but I couldn't get it to work. Please help. Thanks, Steve |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Personal Ledger Problem
"Steve" wrote:
I want to be able to input my online balance into a cell and then have the cell next to it automatically take the last balance number on my spreadsheet and tell me how much my balance off by. If the current online balance is in A1, then: =INDEX($F:$F, MATCH(9E307,$F:$F)) - $A$1 This assumes that the entire column F is used only for tracking current running balance. (It may also contain some text, e.g. titles and null strings.) Alternatively, replace $F:$F with the specific range that might the current balance or null strings (""), e.g. $F$2:$F$100. The MATCH() expression returns the offset within the range $F:$F of the last cell with a numeric value less than 9E307, just about the largest possible value; ergo, the cell offset of the last numeric value. The INDEX() expression returns the value of the cell identified by MATCH(). the Balance Col contaitns the following formula: =IF(D32&E32="","",F31-D32+E32). Unless you have the "Precision as displayed" calculation option set, it is prudent to round most financial calculations to the smallest coin of the realm, e.g. cents for the US. So I would suggest: =IF(D32&E32="", "", ROUND(F31-D32+E32, 2)) =ROUND(INDEX($F:$F, MATCH(9E307,$F:$F)) - $A$1, 2) The need to round such calculations arises from the internal representation and arithmetic that Excel (and most applications) uses on binary computers (viz. floating point arithmetic). Consequently, most decimal fractions cannot be represented exactly, and small numerical aberrations arise as a consequence. For example, IF(10.1 - 10 = 0.1, TRUE) returns FALSE(!), whereas IF(ROUND(10.1 - 10, 2) = 0.1,TRUE) returns TRUE. ----- original message ----- "Steve" wrote in message ... Here's my problem: I am keeping track of my Deposits and Withdrawls in Excel, so that I have up-to-date info on how much I have in my account. Currently I have it set up so that when a transaction occurs I hard code in date, description,withdrawl amt, or deposit amt and the the Balance Col contaitns the following formula: =IF(D32&E32="","",F31-D32+E32). The next objective I want to concur is that right now I have a check that has not been cashed by the recipient. My only balance is off by the amount of that check. As a check to make sure I have not missed any transactions I want to be able to input my online balance into a cell and then have the cell next to it automatically take the last balance number on my spreadsheet and tell me how much my balance off by. I was trying to do something with IF formulas using that if the balance cell below it is blank then use the cell above it, but I couldn't get it to work. Please help. Thanks, Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows in Excel Ledger problem | New Users to Excel | |||
Problem with Personal.xls | Excel Discussion (Misc queries) | |||
Problem with Personal.xls | Excel Discussion (Misc queries) | |||
problem from Personal.xls | Setting up and Configuration of Excel |