Excel formula

Question

a formula for cell L7 is:

   =IF(E7="CA$H",G7+SUMIF($D$7:$D$63,D7,$B$7:$B$63),G7)

I would like to add to this formula an operator (+-*/) and a number

I want to enter the operator in J7 and the number in K7 so the formula would resemble:

   =IF(E7="CA$H",G7+SUMIF($D$7:$D$63,D7,$B$7:$B$63),G7) {add oper fr J7} K7

what is the syntax needed ?

Alternatively, the operator in J7 & number in K7 could be entered in one cell if more efficient

Intent: The cell is adjusted by the base formula, and then J7&K7 would be manually entered for further adjustment.  Also if need be the oper & number could default to "*" & "1"

Answer 1

Jason/or whoever picks up on this,

Sorry to have to impose further on the same topic, but I am back trying to finalize my project involving this formula. Thanks for your patience.

The current in column K (K7:K62)...

     =IF(E7="CA$H",G7+SUMIF($D$6:$D$62,D7,$B$6:$B$62),G7)

Where :

  E7 = input from another sheet, and equals the word Ca$h or another symbol fed over  {repeated thru E62}

  G7 = input from another sheet, and equals a numeric value     {repeated thru G62}

  Col D = input from another sheet, and equals one of about 12 descriptive text phrase   {repeated thru D62}

  D7 = input from another sheet, and equals the descriptive phrase for that line item   {repeated thru D62}

  B7 =    =IF(E7="CA$H",0,IFERROR((G7-K7)*F7*N7,0))        {repeated thru B62}

  G7 = [see above]

I have added Column J

I would like to add operator (+ - * / ) and factor (any number) in column J

   ex:  *0  to zero out cell in K   or  -200  to reduce cell in K by 200 units   or  /2  to cut the value in cell K by half  etc....

This creates a circular reference in normal operation.  I am trying to get the base formula to calculate and then a second pass to calculate K using column J input

Answer 2

Sorry, it is VBA code, so if you save it in a module in the workbook, it will be available for every sheet in the whole workbook.

Answer 3

Jason,

Thank you very much for the prompt reply.

"Save this VBA UDF in the spreadsheet:"

Do I save it anywhere in the spreadsheet?  workbook? worksheet?

Answer 4

Save this VBA UDF in the spreadsheet:

Function Eval(Ref As String)

  Application.Volatile

  Eval = Evaluate(Ref)

End Function

And then use it in cell L7 like this:

=Eval(IF(E7="CA$H",G7+SUMIF($D$7:$D$63,D7,$B$7:$B$63),G7)&J7&K7)