I am trying to connect two If formulas so that I can drag the formula down the entire column. I created a mock spreadsheet to show an example. I need to make a third column where column C and D are combined. Column C is a debit and Column D is a credit. To complete the spreadsheet I had to stop and set up a new formula. The first picture shows the formulas that worked separately. The second picture contains the results. Am I using the correct formula? If so, how can I make it work?
Thank you
@Sharon21 "Is a debit always = 1 and credit always = 0 in excel?" No. This is how I setup the formulas to determine which accounts have a "normal" debit balance, and which ones have a "normal" credit balance. I used 1's and 0's as the return values for the CHOOSE function because it's shorter/simpler than using TRUE's and FALSE's (the IF function will interpret 1 as TRUE and 0 as FALSE). The main goal is to use a formula that will return TRUE or FALSE, to be passed to the logical_test parameter of the IF function.
The generic syntax for the IF function is:
=IF(logical_test, [value_if_true], [value_if_false])
In the examples I've used thus far, the [value_if_true] is Debit-Credit and the [value_if_false] is Credit-Debit. As such, we need to use a formula for the logical_test that will return TRUE (or 1) for an account with a "normal" debit balance (Asset or Expense accounts), and FALSE (or 0) for an account with a "normal" credit balance (Liability, Equity or Revenue accounts). This is where the CHOOSE function comes in.
The generic syntax for the CHOOSE function is:
=CHOOSE(index_num, value1, [value2], ...)
The index_num determines which value is returned. You can specify up to 254 values to choose from. For example, if the index_num is 4, the 4th value is returned:
Since your account codes appeared to follow a pattern, where the first number after the "O" indicates the account type/class (O4 = Sales; O6 = Cost; O7 = Expense), I made the assumption, based on my own accounting experience working with Sage 50, that all accounts beginning with O1 = Assets, O2 = Liabilities, O3 = Equity, O4 = Revenue and O5 thru O9 = Expenses. As such, I used the MID function to extract the 2nd character to be passed to the index_num parameter.
For example, if the account code "O4020216" is used in cell A2, the formula will evaluate as follows:
=IF(CHOOSE(MID(A2, 2, 1), 1, 0, 0, 0, 1, 1, 1, 1, 1), C2-D2, D2-C2)
=IF(CHOOSE(MID("O4020216", 2, 1), 1, 0, 0, 0, 1, 1, 1, 1, 1), C2-D2, D2-C2)
=IF(CHOOSE("4", 1, 0, 0, 0, 1, 1, 1, 1, 1), C2-D2, D2-C2)
=IF(0, C2-D2, D2-C2)
=IF(FALSE, C2-D2, D2-C2)
=D2-C2
=150-0
=150
Having said that, if I was wrong in assuming that ALL of your account codes follow a strict numbering pattern, the XLOOKUP method that I demonstrated in the sample workbook attached to my previous reply can be used (please download and open that file to see how it works).
The same basic IF formula is used, but instead of using CHOOSE/MID as the logical_test, use XLOOKUP to return the matching value from a separate lookup table. For example...
=IF(XLOOKUP(A2, tblAccounts[Code], tblAccounts[IsDebit], TRUE), C2-D2, D2-C2)
...where the lookup table was formatted as a structured Excel table named tblAccounts.
If further information is required, please see:
Accepted Solutions
@Sharon21 "Is a debit always = 1 and credit always = 0 in excel?" No. This is how I setup the formulas to determine which accounts have a "normal" debit balance, and which ones have a "normal" credit balance. I used 1's and 0's as the return values for the CHOOSE function because it's shorter/simpler than using TRUE's and FALSE's (the IF function will interpret 1 as TRUE and 0 as FALSE). The main goal is to use a formula that will return TRUE or FALSE, to be passed to the logical_test parameter of the IF function.
The generic syntax for the IF function is:
=IF(logical_test, [value_if_true], [value_if_false])
In the examples I've used thus far, the [value_if_true] is Debit-Credit and the [value_if_false] is Credit-Debit. As such, we need to use a formula for the logical_test that will return TRUE (or 1) for an account with a "normal" debit balance (Asset or Expense accounts), and FALSE (or 0) for an account with a "normal" credit balance (Liability, Equity or Revenue accounts). This is where the CHOOSE function comes in.
The generic syntax for the CHOOSE function is:
=CHOOSE(index_num, value1, [value2], ...)
The index_num determines which value is returned. You can specify up to 254 values to choose from. For example, if the index_num is 4, the 4th value is returned:
Since your account codes appeared to follow a pattern, where the first number after the "O" indicates the account type/class (O4 = Sales; O6 = Cost; O7 = Expense), I made the assumption, based on my own accounting experience working with Sage 50, that all accounts beginning with O1 = Assets, O2 = Liabilities, O3 = Equity, O4 = Revenue and O5 thru O9 = Expenses. As such, I used the MID function to extract the 2nd character to be passed to the index_num parameter.
For example, if the account code "O4020216" is used in cell A2, the formula will evaluate as follows:
=IF(CHOOSE(MID(A2, 2, 1), 1, 0, 0, 0, 1, 1, 1, 1, 1), C2-D2, D2-C2)
=IF(CHOOSE(MID("O4020216", 2, 1), 1, 0, 0, 0, 1, 1, 1, 1, 1), C2-D2, D2-C2)
=IF(CHOOSE("4", 1, 0, 0, 0, 1, 1, 1, 1, 1), C2-D2, D2-C2)
=IF(0, C2-D2, D2-C2)
=IF(FALSE, C2-D2, D2-C2)
=D2-C2
=150-0
=150
Having said that, if I was wrong in assuming that ALL of your account codes follow a strict numbering pattern, the XLOOKUP method that I demonstrated in the sample workbook attached to my previous reply can be used (please download and open that file to see how it works).
The same basic IF formula is used, but instead of using CHOOSE/MID as the logical_test, use XLOOKUP to return the matching value from a separate lookup table. For example...
=IF(XLOOKUP(A2, tblAccounts[Code], tblAccounts[IsDebit], TRUE), C2-D2, D2-C2)
...where the lookup table was formatted as a structured Excel table named tblAccounts.
If further information is required, please see: