Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

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

Copper Contributor

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 formulaShows formula resultsShows formula results

Thank you

18 Replies

@Sharon21 What about this simple function?

=ABS(D1-C1)

Harun24HR_0-1706583358860.png

 

 

@Harun24HR 

 

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

 

Glad to know! If it helps then please mark this answer as best response to treat the question as solved.
@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.

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

@djclements; @Harun24HR

This looks like it will work. I will check and follow up. thank you.
@djclements
Does the (A1/100) actually mean the formula is dividing by 100?
@djclements
I posted a couple of questions, but they disappeared. What am I doing wrong?

thanks

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

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

@Sharon21 Would I be correct in assuming the following account numbering system:

 

Begins WithAccount ClassNormal Balance
O1AssetDebit
O2LiabilityCredit
O3EquityCredit
O4RevenueCredit
O5ExpenseDebit
O6ExpenseDebit
O7ExpenseDebit
O8ExpenseDebit
O9ExpenseDebit

 

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

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

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

 

choose_syntax.png

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.

 

xlookup_example.png

 

If further information is required, please see:

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

@djclements 

 

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

@djclements 

 

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

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

 

choose_syntax.png

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.

 

xlookup_example.png

 

If further information is required, please see:

View solution in original post