IF Statement with multiple conditions

I have a workbook that is basically a check register that include these
fields: Deposits, Withdrawals, Ending Balance, Fees, Balance After Fees. The
Fees field is to show charges for overdraft fees (if any), so the statement
in that field says if the ending balance is less than zero, then post 10.00
in the Fees field, if not, post 0.

That part works fine. However, because I have filled my formulas down, in
those rows that there have been no transactions (deposits, withdrawals), if
the last transaction created a negative balance and posted 10.00 in the Fee
field, then it continues to subtract 10.00 all the way down for the remaining
balances, which is incorrect.

I am trying to find a way to write a statement that basically says - If
there are no deposits and no withdrawals then I want the Fees field to be
blank so that it does not subtract from the balance so that the balance just
fills down.

--
Sherry

I doubt a deposit would generate overdraft fees, even if you still had a
negative balance. So I think the test should be balance < 0 and withdrawal >
0.
=if(and(balance_cell < 0, withdrawal_cell > 0),10,"")

"Ladypep" wrote:

> I have a workbook that is basically a check register that include these
> fields: Deposits, Withdrawals, Ending Balance, Fees, Balance After Fees. The
> Fees field is to show charges for overdraft fees (if any), so the statement
> in that field says if the ending balance is less than zero, then post 10.00
> in the Fees field, if not, post 0.
>
> That part works fine. However, because I have filled my formulas down, in
> those rows that there have been no transactions (deposits, withdrawals), if
> the last transaction created a negative balance and posted 10.00 in the Fee
> field, then it continues to subtract 10.00 all the way down for the remaining
> balances, which is incorrect.
>
> I am trying to find a way to write a statement that basically says - If
> there are no deposits and no withdrawals then I want the Fees field to be
> blank so that it does not subtract from the balance so that the balance just
> fills down.
>
> --
> Sherry

Something like:

=if(and(deposits=o,withdrawals=0),0,If(endingbalance<0,10,0))

If deposits/withdrawals ar both ), set to 0 else test endingbalance < 0, if
true set to 10 otherwise 0.

HTH

"Ladypep" wrote:

> I have a workbook that is basically a check register that include these
> fields: Deposits, Withdrawals, Ending Balance, Fees, Balance After Fees. The
> Fees field is to show charges for overdraft fees (if any), so the statement
> in that field says if the ending balance is less than zero, then post 10.00
> in the Fees field, if not, post 0.
>
> That part works fine. However, because I have filled my formulas down, in
> those rows that there have been no transactions (deposits, withdrawals), if
> the last transaction created a negative balance and posted 10.00 in the Fee
> field, then it continues to subtract 10.00 all the way down for the remaining
> balances, which is incorrect.
>
> I am trying to find a way to write a statement that basically says - If
> there are no deposits and no withdrawals then I want the Fees field to be
> blank so that it does not subtract from the balance so that the balance just
> fills down.
>
> --
> Sherry

Maybe like this
=(D2<0)*OR(B2<>0;C2<>0)*10
where D2 is Ending Balance, B2 Deposit and C2 Withdrawal.
Then you get zero in the Fees cells. You can the get a blank instead of zero (if you want) by means of for example conditional
formatting. (white font on white background).

Hans



"Ladypep" <[email protected]> skrev i en meddelelse news:[email protected]...
>I have a workbook that is basically a check register that include these
> fields: Deposits, Withdrawals, Ending Balance, Fees, Balance After Fees. The
> Fees field is to show charges for overdraft fees (if any), so the statement
> in that field says if the ending balance is less than zero, then post 10.00
> in the Fees field, if not, post 0.
>
> That part works fine. However, because I have filled my formulas down, in
> those rows that there have been no transactions (deposits, withdrawals), if
> the last transaction created a negative balance and posted 10.00 in the Fee
> field, then it continues to subtract 10.00 all the way down for the remaining
> balances, which is incorrect.
>
> I am trying to find a way to write a statement that basically says - If
> there are no deposits and no withdrawals then I want the Fees field to be
> blank so that it does not subtract from the balance so that the balance just
> fills down.
>
> --
> Sherry