Jan 29 2024 05:42 PM
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
Jan 29 2024 06:56 PM
Jan 30 2024 07:48 AM
Wow -That is simple and seems to work. I will try it on my real spreadsheet and let you know. Thank you!!!
Jan 30 2024 08:08 AM
Jan 30 2024 08:12 AM
Jan 30 2024 09:18 AM
@Sharon21 You would first need a way to identify the account type (Asset and Expense = normal Debit balance; Liability, Equity and Revenue = normal Credit balance). Does your chart of accounts follow a strict numbering system for the account codes (shown in column A)? It looks like 200 accounts are Assets, 400's are Revenue, and possibly all 500's and above are Expenses... does that mean 100's are liabilities and 300's are Equity?
Just as an example, if my assumptions are correct, you could try the following formula:
=IF(OR(INT(A1/100)=2, A1>=500), C1-D1, D1-C1)
The logical test uses the OR function to determine if the account code in cell A1 begins with a 2 (Asset) or is greater than or equal to 500 (Expense). If the result is true, the formula returns Debit - Credit; if not, it returns Credit - Debit.
I hope that makes sense. Cheers!
Jan 30 2024 09:51 AM
Jan 30 2024 09:55 AM
Jan 30 2024 09:58 AM
Jan 30 2024 10:03 AM
@Sharon21 Yes, that is correct. Then the INT function returns only the integer portion of the results. For example:
=INT(A1/100)
=INT(216/100)
=INT(2.16)
=2
You could also use the LEFT function instead to achieve the same results. For example:
=IF(OR(LEFT(A1)="2", A1>=500), C1-D1, D1-C1)
Note: the "2" is in quotation marks in this example, because the LEFT function returns a text string.
Jan 30 2024 03:43 PM
Jan 30 2024 09:43 PM
@Sharon21 Would I be correct in assuming the following account numbering system:
Begins With | Account Class | Normal Balance |
O1 | Asset | Debit |
O2 | Liability | Credit |
O3 | Equity | Credit |
O4 | Revenue | Credit |
O5 | Expense | Debit |
O6 | Expense | Debit |
O7 | Expense | Debit |
O8 | Expense | Debit |
O9 | Expense | Debit |
If so, here's another variation of the formula you could try, using the CHOOSE and MID functions:
=IF(CHOOSE(MID(A1,2,1),1,0,0,0,1,1,1,1,1), C1-D1, D1-C1)
The MID function extracts the second character from the account code (1-9) and returns the corresponding value from the list of 1's and 0's (read as TRUE or FALSE), which represents the "Normal Balance" of that account (1 = Debit, 0 = Credit).
Another possible method is to use a lookup table for your Chart of Accounts, which can also double as a data validation list for the account code field in your transactions table. Please see the attached sample workbook, where I've outlined various options using If/OR/MID, IF/CHOOSE/MID, IF/INDEX/MATCH and IF/XLOOKUP...
Jan 30 2024 11:00 PM
Jan 31 2024 11:38 AM
Jan 31 2024 05:55 PM
Solution@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:
Jan 31 2024 06:32 PM
Jan 31 2024 06:35 PM
Jan 31 2024 06:39 PM
Jan 31 2024 06:43 PM
My final response is in the middle of your responses. I'm not sure how that happened. I don't know if I closed the case.
thanks
Jan 31 2024 05:55 PM
Solution@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: