I am creating a spreadsheet to track rent owed from agents at a real estate company. I have drop-down lists created to pick from the agent and the type of transaction (Payment, Rent Incurred, Credit Issued, Fee Issued, Payment Reversed, Copier Usage). What I want is to just enter the number in the Amount field without making it a negative, and have a formula that will add it or subtract it based on the type of transaction it is. Does that make sense? I've tried several formulas and I cannot figure it out. I want this:
If C2="Payment" OR "Credit Issued", then D2=POSITIVE If C2="Rent Incurred" OR "Fee Issued" OR "Payment Reversed" OR "Copier Usage", then D2=NEGATIVE
Is it possible to do a formula like that in conjunction with a running balance?
Like this (I know the formatting is way off...apologies):
Date Agent Transaction Amount Balance
8/25/15 Mike Payment $150.00 -$150.00
9/1/15 Joyce Rent Incurred $200.00 $ 50.00
9/1/15 Mike Rent Incurred $150.00 $200.00
9/1/15 Chris Rent Incurred $250.00 $450.00
9/6/15 Chris Payment $250.00 $200.00
9/15/15 Joyce Fee Issued $ 25.00 $225.00
9/21/15 Joyce Payment $225.00 $ 0.00
I guess I want my balance formula to be like this: =if(c2,"Payment""credit issued",d2*-1,c2,"Rent incurred""fee issued""copier usage""payment reversed",d2)+c1
Does this make any sense at all???
Aucun commentaire:
Enregistrer un commentaire