Row Product in matrices

[PL_11061936]

Hi, 

Can I get some help how to how to write a function that return all row products in a matrice according example below? 

Second question is how I extract data lower, between or higher then a given number.

[terryhendicott · Accepted solution]

Hi

Like this

Cheers

Terry

[PL_11061936]

Ok, thank you so much for this!

I manage to add more columns to your function. But I don´t manage to add more rows? 

Help please 🙄

Best regards

Per

[terryhendicott]

Hi,

How your function works on more rows is not easy to follow.  You need to provide an example of what happens with three columns and three rows?

Cheers

Terry

[PL_11061936]

Not much. But it is most as I don´t understand what I am doing...

It won´t calculate more then two rows. 

An 3x2 and 3x3 matrice results in 9-result vector, An 3x3 matrice should result in 27-result vector. 

File attached

[Werner_E]

You are very unclear when it comes to describing what the function you ask for should actually do if the matrix has more than two rows.

So we can just poke in the dark ... 😞

Maybe this function is what you are looking for:

Its also not clear which way you would like to "extract" values within a certain range. I assume we are talking about filtering and trimming a vector.
You can make use of the built-in "lookup" function to do so:

Prime 10 worksheet attached

[PL_11061936]

Sorry If I am unclear. Probably because I thought I could grip how to build the function in general terms, and then for me to evolve it. Also it´s kind of hard to explain.

The base is a gambling game in Sweden (called "stryktipset") of thirteen soccer matches that can end with three different results. So we have a matrice of 3 colums and 13 rows. This can be just short of 1,6 miljon different rows, 3^13. A lot of rows!!

The game is to "guess" with some knowledge of how each and every game will end. A winn for the home team, a draw or a winn for the away team (1 X 2).

The value for every game is set on how people actually think the game will end. So if, for simplicity, all participants devides their bets 1/3 on game 1 the value for home winn, draw and away winn will be 33% each.

Soccer is a fun game since statistics don´t apply. Although I have created a theory, from statistics, that to be single winner of a game you shall have a inverted product of one row approx 20 miljoner. And you want to be alone with thirteen correct signs since the more that have all right the less you winn. 

So most people play the game thinking they know how the game will end, I want to try to play it using statistics (that don´t apply). 

I attach an Excel spreadsheet that might give you some clarity. It is of 256 rows. With Mathcad I would at the end like to sort out the rows, among the 1,6 miljon, that are in the region of a inverted product of 15-20 miljons. 

Do I in any way make my self clearer? 

[Werner_E]

Hmmm, actually I see no connection of your description of that bet game and your initial demand for a function which multiplies matrix rows with values found in other rows of the same matrix.

I was hoping for a more detailed description of the mathematical working of the function you are looking for and some specific examples (input matrix and expected output vector).

At least I thought you would look at the functions I posted and explain why it does not do what you expect.

Haven't looked at your Excel sheet but I rather would prefer a detailed verbal description of how the desired Prime function should work, i.e. what exactly this function is supposed to do.

[PL_11061936]

Thank you, 

For a verbal description I am back to my general terms again. But your function will most likely work.

[PL_11061936]

Now I see where we get lost in understanding each other for my purpose. If we focus on M2 the products shall be

so the entire row shall be calculated. Do you get me then?

[Werner_E]

OK, at least one example is better than none.

I guess I got you.

So this function should do it:

Prime 10 worksheet attached

[terryhendicott]

Hi,

In the enclosed Prime 10 file is a Prime version of your spreadsheet.  It tests all combinations not just 256.

I have copied the values from your spreadsheet to a table to avoid path problems between computers.

Cheers

Terry

[terryhendicott]

Hi,

Small change where the combinations that gets highest and lowest chance are defined.

Cheers

Terry

[PL_11061936]

Thank you Terry,

I am starting to come to the conclusion that this is not workable. For what I after this wanted to do is to extract those rows, of thirteen games that that have the inverted product of approx 20 millions. But now I see that there are close to 200 rows with a product 19,99 to 20 millions. (See my sheet attached to Werner_E published after this messege.)

So, what I want to do is make a function that 

if Tips256(i,j) equals "one", "draw" or "two"  extracts the procent and creats the product. In my attachement resulting in a 256 row vector. 

But if possible addjustable to create the number of rows that is created. See examples in attached sheet.

[PL_11061936]

I am starting to come to the conclusion, thank´s to your sheet, that this is not workable. For what I after this wanted to do is to extract those rows, of thirteen games that that have the inverted product of approx 20 millions. But now I see that there are close to 200 rows with a product 19,99 to 20 millions.

Attach is a adjusted version of your sheet. I am not sure what you do whit the trunc(round()) function. But my numbers get strange after that.

Also see messege to Terry. 

[Werner_E]

Its not trunc(round()) but trunc(rnd()). I am just creating a 13x3 matrix with random percentage values with just 1 decimal. So I let Prime create a (real!) random number in the range from 0 up to 1000, trunc makes an integer and the division by 1000 brings it in the range from 0 to 1 with at most 3 significant digits which means that when displayed as percent value it will have just one decimal.

You should not need this at all if you use your matrix with real world values.

I am not sure what you mean by "your" numbers get strange.. If you mean the error when calculating Q: You changed my N:=1/M to just N:=M. So the values in N are still in the range from 0 to 1.. This results i the values of P to be rather low, max value being just 0.063. And when you calculate Q you try to exctract all values from 1.9*10^7 to 2*10^7. Hard job if the max value is just 0.063. The error message also clearly states that no suitable values could be found.

BTW I am not sure why you would watch the product of the percentage values. The product of the reciprocal values can be drawn down to a huge result by just one tiny percentage value - no matter what the other 12 values may be. Just one percentage value of zero will make jump the product of reciprocal values up to infinity (and of course would yield an error in Prime).

Maybe just using the sum or the mean value of the percentage values will do a better job?

[terryhendicott]

Hi,

Can we address a number of issues of your use of mathcad not the game problem.

In an input table each column is a vector with the name at the top the name of the column vector variable.

In your definition of Tips192ij you get a double.  the use of i,j index on left hand side gets a 13x3 matrix of 13x3 matrices.  Because "one" is a 13x1 matrix each of the matrixes as elements of Tip192ij is also sometines a 13x1 matrix.

\ 

To work around this error define the vectors in the table with different names

Define the matrices Tip192 withou use of i,j on left hand side

Cheers

Terry

[PL_11061936]

Hi Terry,

Thank you for your efforts. I am trying to solve my use of Mathcad, for statistic purpose. Not the game problem. But I feel I don´t get myself understood. 

And I frankly don´t know how express this to get my self understood. Partly because maybe I use the word "row" for two different meanings. The Row of the indata 3x13 matrice and the Row of the outdata vector that maybe shall be called the column of the outdata vector, containing 13 rows.

I try once more. Me making the Tips128, Tips192 or 256 are just possibilities of different systems. 

Thinking of it the result should be expressed as a matrice as well. 13x256 matrice, or 13x192 or 13x128 or 13x384 matrice for examples

We have 13 rows and 3 columns as indata. In every row you have to asign at least one sign "one", "draw" or "two". But you can asign up to all signs in the row

Two examples

Below you see the indata system example. I have marked the five rows where there is only one sign with yellow and the eigh rows where there is two signs with green. This resulting in an 256-column system

Below you see the indata system example. I have marked the five rows where there is only one sign with yellow and the seven rows where there is two signs with green and finally one row where there is three signs. This resulting in an 384-column system.

There are many other possible column-systems between one ( one sign /row) up to 41472 (nine rows of two signs and 4 rows of three signs).

In the Excel sheet i provided you then can see the results of 13x256 matrice. Where Each and every column then results in the inverted product of the procentage in the column. Make sence?

If possible, I would like to use Mathcad to make the 13xYYY matrice depending on how many signs I want to use in every row. And exactly as in the Excel then see mean, median, max and min values of those.

[terryhendicott]

Hi,

Have got it now.  Here is sheet that gets the 256 combinations (or how many you define) and gets answers equivalent to Excel

[terryhendicott]

Hi,

Have added some more usability so the result of each game is also kept (in this example 13x256 matrix).

Now you can plot results and examine the maximum and minimum game result make up.

[terryhendicott]

And Version 5

[PL_11061936]

THANK YOU Terry!!

This works. This really does what i want. You made it! Thank you!

[PL_11061936]

This is what it looks like now.

Once again, thannk you for your help!

[PL_11061936]

Hi Terry,

I am thinking if there is a possibility to evolve this and extract the columns with inverted products of 15 to 25 millions and put them in a new matrice presented as you did with UC(out())?

Thank´s!

/Per