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
    Possible duplicate of [Excel function that evaluates a string as if it were a formula?](http://superuser.com/questions/253353/excel-function-that-evaluates-a-string-as-if-it-were-a-formula) – Raystafarian Apr 07 '16 at 20:11
  • Try in define name give a name and in Refers to write:Evaluate(c2 & & VLOOKUP(A3,E2:F3,2) & B2) –  Apr 07 '16 at 20:41
  • 1
    If the limit of what you're trying to do is use + or - based on Debit or Credit, it would be a lot simpler just to use an IF. – fixer1234 Apr 08 '16 at 00:26

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,583
  • 11
  • 60
  • 89