I wood like to build a checkbook register using a single column for both “Deposits” & “Withdrawals”. Is there a way with a formula that when I select the transaction Type to be “Withdrawal” that when I enter the amount, it will automatically be negative(-$1.00). Also when the selection is “Deposit”, the amount entered will be automatically positive($1.00).
I will attach a Test File to use if there is a possible answer for me.
Thank you,
David
You can try this formula in D3 and copy it down:
=D2+IF(B3="Withdrawal",-C3,C3)
Riny,
Thank you very much, that formula works very well, but I forgot that I will need another transaction type to result in a negative value; that being Transfer_Out. Can the formula be modified to incorporate this? I added a drop-down in my transaction column of the sample spreadsheet, and I will attach the updated file.
Thank you,
David
No problem. Then use this:
=D2+IF(OR(B3="Withdrawal",B3="Transfer_Out"),-C3,C3)
alternatively, if you only have "Deposit" for all incoming amounts use this:
=D2+IF(B3="Deposit",C3,-C3)
Also, if you would like the visual you can use conditional formatting using this formula:
OR($B3="Withdrawal",$B3="Transfer_Out")
and color the text red and using the custom number format $(x,xx0.00). See attached to see what I mean.
Riny,
I am in need of some additional help. Your last formula works Great! However, when I build a an Analysis sheet using Sumifs formula, I run into a problem. Several times per year I have credit card credits(refunds) due to a return. I have added CC_Refund in my Type Drop Down, but the sumifs is built with “Withdrawal”. Is there a way to add to conditions in the Sumifs, both “Withdrawal” & “CC_Refund”. One thing though, the amount associated with the CC_Refund needs to be added back to the register and to the Analysis in the Sumifs. I am adding an updated file showing a sample of what I am trying to do.
Thank you much,
David
Then you would have to add another SUMIFS statement to the formula that deducts the values for "CC_Refund". I've done that in the attached file in the E4 on the Analysis sheet. See attached.
Note that you also need to apply the Categories consistently. In the cells marked yellow you had "Software " (with a trailing space). I assumed you wanted "Software" (without the trailing space).
Personally, I believe that this method becomes quite cumbersome to maintain. I favour organising the data with positives and negatives or in separate columns for IN and OUT, rather than having to write long formulas that need capture all sorts of exceptions. You can even include budget information in the table itself or a separate one and then create pivot tables based on Types or IN/OUT to create the kind of reports you are dealing with.
