3

I have a spreadsheet whereas I have a string value in one column, and I have an amount value in another column, and a balance value in yet another column. I have a VLookup array to determine the mathematical operation in an Adjusted Balance column so I can verify the results will be correct, before I set this up in SQL to adjust balances. Basically, spreadsheet looks like this:

   A             B            C               D           E        F
TransCode | TransAmount | AccountBalance | AdjBalance | Array1 | Array2 |
Debit       $20.00        -$75.00                       Debit    -
Credit      $20.00        -$55.00                       Credit    +
Credit      $15.00        -$40.00
Debit       $125.00       -$165.00

In my AdjBalance, I've created a formula with vlookup that looks like so:

=C2 & VLOOKUP(A3,E2:F3,2) & B2

But the results I get are -55+20. I've tried wrapping in EVALUATE or EVAL, but Excel doesn't recognize this function.

Question is, is there a function to force the string to a mathematical equation? I've looked and Eval is the only function I can come up with, but maybe I'm not wording my search in the right way.

Jim
  • 31
  • 2

2 Answers2

0

Define Name for example Balance and in Refers to write the following

=EVALUATE(Sheet1!C2 & VLOOKUP(Sheet1!A3,Sheet1!$E$2:$F$3,2,FALSE) & Sheet1!B2)

in the cell where you want the result write = Balance and you can drag it down.

0

As fixer1234 pointed out - it would be a lot more simple using an IF. You can just do away with columns E and F

=IF(A2 = "Debit",C2-B2,IF(A2 = "Credit",C2+B2,"No Transcode"))
Community
  • 1
Raystafarian
  • 21,963
  • 12
  • 64
  • 91