function calculation of cells

[iaudio1233]

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	)	x	4

<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

 

[Eisasuarez]

Hi Peter_SSS


I have been trying so hard to get this


Please advise if this is correct and if i am going in the right direction with understanding this (the double transpose) and the replace part


r = A2:G2 - returns a 2d array like this (1 to 1, 1 to 7)


if i transpose r, this will still return a 2d array but just flip the columns to rows - so it now becomes (1 to 7, 1 to 1) <<<<still a="" 2d="" array


The join function cant work with 2d so by transposing a column which is 1 to 1 this will eliminate to 1 to 1 so it now becomes (1 to 7)
however if there is more columns then you will not be able to convert to a 1d array


e.g (1 to 5, 1 to 7)<<<< if i were to transpose this it will become (1 to 7, 1 to 5). If i were to traspose again it will flip again to (1 to 5, 1 to 7) and will continue to work in this manner
but it will never be a 1d array no matter how many times i transpose this.


but if i had it like this (1 to 5, 1 to 1) by transposing, it will become (1 to 1, 1 to 5) or will it be just (1 to 5) or (1 to 1, 1 to 5)


i am trying to understand when is it a case that it will flip the rows and columns and when is it a case of making it a 1d from a 2d</still>

 

[Eisasuarez]

Hi Me again,

i did this as a test

Hopefully this will explain a bit more of what i am trying to understand. The main this is when does it flip columns to rows and vice versa and when does it eliminate to be 1d etc..

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

Many Thanks

If you could be so kind again to explain with a bit code to and explanation to step through, hopefully i can visually see how it all is functioning in the background

Function Calc(r As Range)

' i did this as a test
Debug.Print r.Address '$B$1:$I$1
Calc = r ' (1 to 1, 1 to 8)
Calc = Application.Transpose(r) ' flips over (1 to 8, 1 to 1)
'now when i do transpose again it still remains (1 to 8, 1 to 1) and will not eliminate to become (1 to 8)
Calc = Application.Transpose(r) ' still the same (1 to 8, 1 to 1)
Calc = Application.Transpose(r) ' still the same (1 to 8, 1 to 1)


End Function


Function Calc2(r As Range)

' i did another test
Debug.Print r.Address '$B$1:$I$1
Calc2 = r ' (1 to 1, 1 to 8)
Calc2 = Application.Transpose(r)
Calc2 = Application.Transpose(Application.Transpose(r)) ' converts to 1d ( 1 to 8)
Calc2 = Application.Transpose(r) ' converts back to 2d (1 to 8, 1 to 1)
Calc2 = Application.Transpose(r) ' still the same (1 to 8, 1 to 1)


End Function

 

[Eisasuarez]

Thank you peter and i hope you dont mind me asking the question as i am really eager to understand this

thank you

 

[Peter_SSs]

Hopefully this will explain a bit more of what i am trying to understand. The main this is when does it flip columns to rows and vice versa and when does it eliminate to be 1d etc..

I'm not sure I'm up to a good explanation of that. In any case, why not use the shorter 'Index' method suggested by Rick in post #7 to get the 1-D array?

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

Go into the Visual Basic window and use the Help facility to search for "Replace Function". There's a clear and concise explanation of those arguments there.

 

[Eisasuarez]

Hi Peter

thank you

i can go with ricks solution but I just wanted to understand the transpose of the dimension part

 

[iaudio1233]

Hi, there,

I have tested thoroughly for different combinations and find the function works without any errors .
Amazingly, more than I expected is that when the formula extends to more eg. 1+2+3+4x7+7 with the brackets placed with different combinations.... works fine till now.
now the function is placed in my xla as precious as pearl which save me lot of time.

Thanks again
** still learning the transpose part... not easy for me.

 

[RoryA]

The main this is when does it flip columns to rows and vice versa and when does it eliminate to be 1d etc..

Transpose always flips rows/columns. (that's what it's for )

The conversion to 1D occurs when you transform to a "one row" array. Because assigning the value of a multiple cell range to a variant always creates a 2D array, you have to transpose twice to get a 1D array if you start with a one row range.

 

[Eisasuarez]

Hi RoryA

what do you mean by 1 row array and 1 row range and assigning value of multiple cell range creates 2d?

is there any chance you could give some code example with notes so I can follow what you mean

I am aware transpose flips rows and columns

 

[Eisasuarez]

Hi RoryA

this thread is more or less exactly the same thing I am struggling with. Thank you mahmed1 for breaking it down better than me

http://www.mrexcel.com/forum/excel-...standing-transpose-data-2d-1d-vice-versa.html

 

[RoryA]

I assume you two work together?

#1: If you use this code:

Dim Data
Data = range("some range").value

Data will always be a 2 dimensional array as long as the range in question is more than one cell. (It doesn't matter if the range is one row, one column, or multiple rows/columns you will always get a 2D array.)

#2: When you transpose, if the result would be one row, you get a 1D array.

So if the range is one column (e.g. A1:A10), when you transpose it you get 1 1D array.

If the original range is one row, you start with a 2D array (see #1) so you have to transpose twice - the first time you get a 1 column array, which is 2D, and the second transpose switches to a 1 row, so you get 1D.