Formula based on variable operand

[zdinath]

Hi,

I hope there is a way of doing this...
I'm attempting to create a formula by drawing the operand from a cell within my workbook, thus allowing for dynamic calculation to be created.
Will probably be easier to explain using a simple example.

I want to have an Input 1 in one cell...say 10 in cell A1
then have an operand in another cell...say + in cell A2
and then Input 2 in another cell...say 20 in cell A3

I then want to use these variables in cell A4 to create the formula...so basically A4 would be something along the lines of "=concatenate(A1,A2,A3)" but not having it all converted to text and outputting '10+20' but rather the mathematical answer of 30.

Please tell me it's possible....
Thanks
Z

 

[Jonmo1]

Probably need VBA for that.

Here's a function.
Press ALT + F11 to open VBA editor
Click Insert - Module
Paste the following

Public Function CustFormula(MyStr As String)
CustFormula = Evaluate(MyStr)
End Function

Now use a formula in a cell
=CustFormula(concatenate(A1,A2,A3))

Or
=CustFormula(A1&A2&A3)

The & symbol does the same thing as the concatenate function.


Hope that helps.

 

[zdinath]

Hmmm...I was really hoping to not have to use any VB and thus make the workbook into a Macro Enabled one. It seems to just scare people here at my company .
But if there is no other way...then I guess I have no choice.
Thanks for the help...much appreciated.

Regards,
Z

 

[Jonmo1]

It depends on how complicated you're going to get...

If it's just a simple 2 values and 1 operand(either + - * or /)
Then it can be done fairly easily.

But if you're going to get more complicated than that, then VBA is definately more appropriate.

 

[Jonmo1]

For your specific example, try

=IF(A2="+",A1+A3,IF(A2="-",A1-A3,IF(A2="*",A1*A3,IF(A2="/",A1/A3))))

 

[zdinath]

Nah...the example I used was a very simplified one just to get the concept across. I've got numerous Inputs being looked up from different sources and then various levels of operations applied to these inputs.
I'm using your VB suggestion and its working brilliantly well...Thanks a lot for the help.

Z