If, Or If

[tdavis514]

I need help with an IF statement. I'm trying to set up a personal budget. Column A=Date, B=Expense Amount, C=Category, D=Description, E=Credit Card, F=Savings, G=Checking, H=Car Loan. I'm trying to set up some IF statements so that all I have to do is fill in Expense, Category, and Description and have everything else update automatically.

Here's the IF statement for Credit Card column E:
--IF (C="Payment" AND D="Credit Card") subtract E-B, IF (C<>"Payment") add E+B, and IF (C="Payment" and D="Car Loan") or (C="Income") then E=E.

Here's the IF statement for Savings, column F:
--If (C="Income") add F+B OR if (C="Transfer") subtract F-B, but if (C<>"Income" or "Transfer") then F=F.

Here's the IF statement for Checking, column G:
--If (C="Transfer") add G+B OR if (C="Payment") subtract G-B, but if (C<>"Payment" or "Transfer") then G=G.

So I know what I want to say, but I'm having trouble coding it. Help is greatly appreciated.

 

[Jameo]

Ok assuming your column headings are in the first row, and your values start in the second row

You would need to write these statements in blank cells, looking at the above it appears you have E filled with data?

Colum F:

=if(and(C2="Payment",D2="Creditcard"),E2-B2,if(C2<>"Payment",E2+B2,if(and(C2="Payment",D="Car Loan"),E2,"No Match"))))

=if(C2="Income",F2+B3,if(C2="Transfer",F2-B2,if(or(C2<>"Income",C2<>"Income"),F2,"NoMatch")))


I am a little confused about your data, but hopefully the above gives you a general idea of how to form the and/or statements.

 

[GlennUK]

There is conflicting information ...
IF (C<>"Payment") add E+B, .... (C="Income") then E=E

If C="Income" then the tests are true for both cases. I haven't read the other cases yet.

 

[tdavis514]

Date_____Exp_____Cat_____Desc_____Credit Card_____Savings_____Checking_____Car Loan
Starting Balance:_______________$(1,768.86)___$100.00______$-_____$(13,053.65)
5/23/___$39.08 ___Food_____PC_________$-__________$100_________$-_____$(13,053.65)


That's what Rows 1-3 look like...best I can do.

@Glenn, let me try it this way.. there are 4 criteria for column E
1) If C=Payment and D=Credit Card, then E should decrease
2) If C=Payment and D<>Credit Card, then E should remain the same
3) If C=Income, E should remain the same
4) If C= anything else, E should increase.

 

[tdavis514]

Ok assuming your column headings are in the first row, and your values start in the second row

You would need to write these statements in blank cells, looking at the above it appears you have E filled with data?

Colum F:

=if(and(C2="Payment",D2="Creditcard"),E2-B2,if(C2<>"Payment",E2+B2,if(and(C2="Payment",D="Car Loan"),E2,"No Match"))))

=if(C2="Income",F2+B3,if(C2="Transfer",F2-B2,if(or(C2<>"Income",C2<>"Income"),F2,"NoMatch")))


I am a little confused about your data, but hopefully the above gives you a general idea of how to form the and/or statements.

All of my data is in columns B, C, and D.

I got your first formula to work for column E by tweaking it a bit.

'=IF(AND(C5="Payment",D5="Credit Card"),E4+B5,IF(OR(D5="Car Loan",C5="Income"),E4,IF(C5<>"Payment",E4-B5,)))

 

[GlennUK]

All of my data is in columns B, C, and D.

I got your first formula to work for column E by tweaking it a bit.

'=IF(AND(C5="Payment",D5="Credit Card"),E4+B5,IF(OR(D5="Car Loan",C5="Income"),E4,IF(C5<>"Payment",E4-B5,)))

That would reset Credit Card to zero if Payment is in column C and column D holds something other than Credit Card. Surely you don't want that?