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 |
IF Statement with multiple conditions
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 |
IF Statement with multiple conditions
Something like:
=if(and(deposits=o,withdrawals=0),0,If(endingbalan ce<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 |
IF Statement with multiple conditions
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" skrev i en meddelelse ... 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 |
All times are GMT +1. The time now is 11:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com