function calculation of cells

iaudio1233

Board Regular
Joined
Mar 7, 2014
Messages
158
Hi, I have some calculation cases as follows which is common in primary school lessons.
1+2+3+4
1
x(2+3)
(1+2)x4

<tbody>
</tbody>

there are about 64 combinations to calculate the cells. like +++ ++- ++x ++÷...............
I used "nested if" for case 1 like ( =if(and(b1="+",d1="+",f1="+"),a1+c1+e1,"")&if(and(b1="+",d1="+",f1="-"),a1+c1-e1,"")&if(...................................)
since there are about 10 cases as above as the brackets position change , and each has about 64 combinations. the formula becomes tremendously cumbersome.
Please advise how to shorten the nested ifs or create UDF to solve such problems.

Thanks
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Welcome to the MrExcel board!

You could try this UDF. Note that it is designed to be used on a range consisting of a single row and more than one cell, but has no checking that the input range conforms to that structure.
Code:
Function Calc(r As Range)
  Calc = Evaluate(Replace(Replace(Join(Application.Transpose(Application.Transpose(r))), "x", "*", 1, -1, 1), "÷", "/", 1, -1, 1))
End Function

Excel Workbook
ABCDEFGH
21+2+3+410
31x(2+3)5
4(1+2)x412
562x412
65x2+717
777
Calculate
 
Upvote 0
Peter,
Thanks one thousand times !! I have struggled for few months with 28 cells for calculation !!! Thanks , Thanks
 
Upvote 0
Cheers, glad it helped. Thanks for letting us know.
 
Upvote 0
Cheers, glad it helped. Thanks for letting us know.


Hi petersss

what an amazing function and code you have there

is there any chance you can brake down what the code is doing so i can leatn the purpose and needs of each function, i.e transpose x2 replace x2 join eval etc

realy curious to know

many thanks
 
Upvote 0
is there any chance you can brake down what the code is doing so i can leatn the purpose and needs of each function, i.e transpose x2 replace x2 join eval etc
I'll give it a try.

Calc = Evaluate(Replace(Replace(Join(Application.Transpose(Application.Transpose(r))), "x", "*", 1, -1, 1), "÷", "/", 1, -1, 1))

Application.Transpose(Application.Transpose(r))
I'm trying to get each of the cell values into a 1-dimensional array. Normally, if you read the values of a range into an array, it creates a 2-dimensional array.
A single Application.Transpose would covert a column of cells into a 1-dimension array, but we have a row of cells rather than a column.
Using a double Application.Transpose converts the row of cell values into the elements of a 1-dimensional array

Join(Application.Transpose(Application.Transpose(r)))
This now joins the elements of the array into a single text string, separated by spaces. So for the row 3 example shown, we now have:
"1 x ( 2 + 3 )"

Replace(Join(Application.Transpose(Application.Transpose(r))), "x", "*", 1, -1, 1)
We now replace in the string any "x" characters with "*" characters since that is what Excel/vba uses for multiplication.
So we now have the string:
"1 * ( 2 + 3 )"

Replace(Replace(Join(Application.Transpose(Application.Transpose(r))), "x", "*", 1, -1, 1), "÷", "/", 1, -1, 1)
We now replace in the string any "÷" characters with "/" characters since that is what Excel/vba uses for division.
Since there are no "÷" characters in this particular row, we still have the string:
"1 * ( 2 + 3 )"

Evaluate(Replace(Replace(Join(Application.Transpose(Application.Transpose(r))), "x", "*", 1, -1, 1), "÷", "/", 1, -1, 1))
Evaluate now does exactly that, giving 5 for this example.


As a matter of interest, we could have taken a slightly different approach and used the following to achieve the same result:

Calc = Evaluate(Join(Split(Join(Split(Join(Application.Transpose(Application.Transpose(r))), "x"), "*"), "÷"), "/"))
 
Upvote 0
You could try this UDF. Note that it is designed to be used on a range consisting of a single row and more than one cell, but has no checking that the input range conforms to that structure.
Rich (BB code):
Function Calc(r As Range)
  Calc = Evaluate(Replace(Replace(Join(Application.Transpose(Application.Transpose(r))), "x", "*", 1, -1, 1), "÷", "/", 1, -1, 1))
End Function
You do not need to double Transpose when joining across a single row of contiguous cells, you can use the Index function instead. This...

Join(Application.Index(r.Value, 1, 0))

can be used in place of what I highlighted in red (note, you must specifically specify the Value property of the range when using the Index function this way). Here is your function written using what I suggested...
Rich (BB code):
Function Calc(r As Range)
  Calc = Evaluate(Replace(Replace(Join(Application.Index(r.Value, 1, 0)), "x", "*", 1, -1, 1), "÷", "/", 1, -1, 1))
End Function
 
Upvote 0
Rich (BB code):
Function Calc(r As Range)
   Calc = Evaluate(Replace(Replace(Join(Application.Transpose(Application.Transpose(r))), "x", "*", 1, -1, 1), "÷", "/", 1, -1, 1))
 End Function
You do not need to double Transpose when joining across a single row of contiguous cells, you can use the Index function instead. This...

Join(Application.Index(r.Value, 1, 0))

can be used in place of what I highlighted in red (note, you must specifically specify the Value property of the range when using the Index function this way). Here is your function written using what I suggested...
Rich (BB code):
Function Calc(r As Range)
  Calc = Evaluate(Replace(Replace(Join(Application.Index(r.Value, 1, 0)), "x", "*", 1, -1, 1), "÷", "/", 1, -1, 1))
End Function

While I would use your function from Message #2 as modified by me in Message #7 (both shown above) as I think it is "cleaner", I thought maybe you would like to see this alternative where the replacing of the x and ÷ signs has been moved from the VBA world to the worksheet world...
Rich (BB code):
Function Calc2(R As Range)
  Calc2 = Evaluate("1*SUBSTITUTE(SUBSTITUTE(LOWER(""" & Join(Application.Index(R.Value, 1, 0)) & """,""x"",""*"")),""÷"",""/"")")
End Function
The LOWER() function call, which I have highlighted in red above, was included because SUBSITUTE is case sensitive and I did not know if upper case X's might ever be used. If not (that is, if the x is always lower case), then the LOWER( and its closing ) character can be removed.
 
Upvote 0
You do not need to double Transpose when joining across a single row of contiguous cells, you can use the Index function instead. This...

Join(Application.Index(r.Value, 1, 0))
That's good, slightly simpler visually and a marginal improvement in speed.

But the following results in #VALUE for me. What am I missing?
Rich (BB code):
Function Calc2(R As Range)
  Calc2 = Evaluate("1*SUBSTITUTE(SUBSTITUTE(LOWER(""" & Join(Application.Index(R.Value, 1, 0)) & """,""x"",""*"")),""÷"",""/"")")
End Function
 
Upvote 0
Hi peter

thank you so much

i am still trying to understand the double transpose part

is there any chance you can give an explanation or bit of code where I can test what it looks like by stepping through code so I can see a visual code and see why it is needed. I am just trying to grasp this part and see what you mean by converting to 1d with 2 transpose.

and with the replace part, how is the 1, -1, 1 working? Don't understand how that part of the function works.

again thank you so much sir
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,560
Latest member
Torchwood72

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top