Running account balance checking for transaction type matches in Defined Name lists

[andy2017]

Hi,

I'd be grateful for any advice on a problem that I've been struggling with - despite searching high and low for clues.

I have several columns in a Data Table that provide running account balances, with transactions on each line crediting or debiting one or more accounts. I have a column for the transaction values and one specifying the transaction type (e.g. AccountX Interest).

I 'm trying to simplify the code used as my workbook is slowing down - so I'm keen to use non-volatile functions etc. and minimise the work Excel has to do. I'd also like to make the workbook easier to edit if the accounts change etc..

I have (in a separate sheet) created Defined Name lists of the transaction types that should trigger either credits or debits to/from each account (e.g. called credits_AccountX and debits_AccountX).

I have also used a Defined Name to create a CellAbove reference, which works fine when tested separately.

I am trying to build a formula for the cells in the account columns that 'says':

1. If the Transaction type is listed in the credits_AccountX Defined Name list, add the Transaction value to the balance in the cell above
2. If the Transaction type is listed in the in the debits_AccountX Defined Name list, subtract the Transaction value from the balance in the cell above
3. If neither of these applies, use the balance in the cell above

I am currently trying to make the following formula work:

=
IF(OR([@[Transaction type]]=credits_AccountX), CellAbove+[@[Transaction value]],
IF(OR([@[Transaction type]]=debits_AccountX), CellAbove-[@[Transaction value]],
CellAbove
))

Looking at the formula builder, this seems to be returning the correct data, but it shows as a #VALUE! error on the sheet.

I'm using Excel for Mac 2016.

Can anyone see where I'm going wrong, or suggest an alternative approach?

Many thanks indeed!

Andrew

 

[hamedghasemi]

hello
send your formula in a1 style(or excel for win) to be readable.

 

[andy2017]

hello
send your formula in a1 style(or excel for win) to be readable.

The formula reflected the use of a Data Table.

In a more conventional format:


=
IF(OR(B2=credits_AccountX), C1+A2,
IF(OR(B2=debits_AccountX), C1-A2,
C1
))


...where:

C1 = Previous balance
A2 = Transaction value
B2 = Transaction type (e.g. AccountX interest)
credits_AccountX = A Defined Name list of transaction types that should trigger a credit to AccountX
debits_AccountX = A Defined Name list of transaction types that should trigger a debit from AccountX


The #VALUE! error seems to come from the use of the Defined Name list, because it disapears if I substitute these with the Transaction types themselves. But I would rather manage these in separate lists of some sort if possible.

Thanks!

 

[hamedghasemi]

hi
dear andy2017 as I understand credits_AccountX and debits_AccountXyour formula are lists of variables not a single variable and you in your formula try to check if a variable is equal to a list of variables!!!
in this case I think you can try vlookup function as below:

=IF(NOT(ISERROR(VLOOKUP(B2,credits_AccountX,1,0))),C1+A2,IF(NOT(ISERROR(VLOOKUP(B2,debits_AccountX,1,0))),C1-A2,C1))

tell me if that works or not.
good luck

 

[andy2017]

hi
dear andy2017 as I understand credits_AccountX and debits_AccountXyour formula are lists of variables not a single variable and you in your formula try to check if a variable is equal to a list of variables!!!
in this case I think you can try vlookup function as below:

=IF(NOT(ISERROR(VLOOKUP(B2,credits_AccountX,1,0))),C1+A2,IF(NOT(ISERROR(VLOOKUP(B2,debits_AccountX,1,0))),C1-A2,C1))

tell me if that works or not.
good luck

Dear hamedghasemi,

Thanks very much for the reply! That did work, first time! It does simplify the code in each cell considerably.

I don't know if VLOOKUP would be slower than using an INDEX/MATCH function (or of other pros/cons for each) - there seems to be conflicting advice about that out there. If you or anyone else has a good understanding of this, I'd be interested as my workbook has been getting slower lately.

Thanks again hamedghasemi. Much appreciated!