mardi 25 août 2015

If/Then formula in Google Sheets - make a number negative?

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