SOLVED

# If formula not working when I try to combine the 2 formulas in Excel

Copper Contributor

# If formula not working when I try to combine the 2 formulas in Excel

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?Shows formulaShows formula results

Thank you

18 Replies

# Re: If formula not working when I try to combine the 2 formulas in Excel

``=ABS(D1-C1)``

# Re: If formula not working when I try to combine the 2 formulas in Excel

Wow -That is simple and seems to work.   I will try it on my real spreadsheet and let you know.  Thank you!!!

# Re: If formula not working when I try to combine the 2 formulas in Excel

Glad to know! If it helps then please mark this answer as best response to treat the question as solved.

# Re: If formula not working when I try to combine the 2 formulas in Excel

@Harun24HR
Since the example deals with debits and credits, some of the results might be a negative number and not the absolute value number. Would I use an if statement to make that happen? For example:
619 Cost Tape 18.00 20.00 -2 2
Column E should be the real answer and column F is the absolute value, and not the correct answer? What would that function look like?
Thank you.

# Re: If formula not working when I try to combine the 2 formulas in Excel

@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!

# Re: If formula not working when I try to combine the 2 formulas in Excel

@djclements; @Harun24HR

This looks like it will work. I will check and follow up. thank you.

# Re: If formula not working when I try to combine the 2 formulas in Excel

@djclements
Does the (A1/100) actually mean the formula is dividing by 100?

# Re: If formula not working when I try to combine the 2 formulas in Excel

@djclements
I posted a couple of questions, but they disappeared. What am I doing wrong?

thanks

# Re: If formula not working when I try to combine the 2 formulas in Excel

@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.

# Re: If formula not working when I try to combine the 2 formulas in Excel

@djclements
With your result you taught me to be more accurate with my example. I was unable to get this formula to work. I tried to reupload a revised example but can't figure out how to do it.
The account number has an "O" before the number. The number is then 7 digits. Please see examples below. O1 is invent, O4 is sales, O6 is cost, O7 is expense.
O1200018
O1200019
04020218
04020219
O6020218
O6020219
O7000061
07000062
Thank you for all your help.

# Re: If formula not working when I try to combine the 2 formulas in Excel

@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...

# Re: If formula not working when I try to combine the 2 formulas in Excel

@jclements

Thank you. I will try again tomorrow. I've used index and match and xlookup before. Both work really well. After I sent this last request, I was wondering if one of those functions would work. The accounts I'm working with is not a standard chart. Again I appreciate your help.

# Re: If formula not working when I try to combine the 2 formulas in Excel

@jclements
The choose, mid formula works. Yeah! I have never used these two formulas, so I had to think it through for a bit to understand the 0,1. debit/credit info. How are you referencing the this section? Is a debit always = 1 and credit always = 0 in excel?

Also would you have time to show me how you would use xlookup with this information? Somehow, I have to reference the debit and credit here. This is what I tried.
=XLOOKUP(MID(A2,2,1),A2:D18,C2-D1,D1-C1)
Thank you again.
best response confirmed by Sharon21 (Copper Contributor)
Solution

# Re: If formula not working when I try to combine the 2 formulas in Excel

@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:

# Re: If formula not working when I try to combine the 2 formulas in Excel

@djclements
You have been very helpful! I appreciate your thorough explanation. I have never used a forum like this and it has been a good source of information. I didn't realize 1=True and 2=False in Excel. I also didn't realize I could download the spreadsheet. I thought it was just a preview. I've had success using index and match and xlookup formulas previously. However, I just couldn't figure this one out.
Thank you very much. I will close the case. I'm going to bookmark this page for future use.

# Re: If formula not working when I try to combine the 2 formulas in Excel

I can't see my final post.  Did you get my response?

@djclements

I see it now.

# Re: If formula not working when I try to combine the 2 formulas in Excel

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

1 best response

Accepted Solutions
best response confirmed by Sharon21 (Copper Contributor)
Solution

# Re: If formula not working when I try to combine the 2 formulas in Excel

@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: