Is excel capable

[Peter1973]

Im wondering if my prob can be solevd using excel.

In sheet 1 A1:G96 there is a table which houses various numbers both +ve and -ve
In sheet 2 A1:A128 there are various times ( always in 15 min increments ) ie 12:15,14:30, 22:00 etc

I need a way to have every possible sequence of times put in A1:A128 ( millions I know )
Here comes the worse bit I need to find which of thoses sequences give me the best result in the table in sheet 1. The best reult is derived by the one which produces the most +ve numbers in the table. So could be counted by total number of cells which are >0 for example.

Not sure if I need a specific programme to do this or if it is something excel is capable of.

Any rocket scientists out there ?

 

[David2e]

How are the sequences combined or presented?

I'm just not clear on what it is... as the way I read it I'm thinking the largest absolute number in the first table will present you with your answer - since you've asked the question I am guessing it's just not that easy.

 

[xenou]

I may be missing something obvious, but what is a ve number? And when you "produce a result from a sequence", how is the result calculated?

 

[David2e]

Although I don't get how the two tables are to relate to each other to form these "sequences"... I can say the 've' is something I've seen and probably used myself.

+ve - positive
-ve - negative

which helps distinguish against plus (+) and minus (-).

 

[xenou]

ahh...I see. Took me way to long to figure that out...

 

[Peter1973]

I will try to be more clear :
In the table in sheet 1 ( which is derived from another table plus what ever information is put into sheet 2 A1:A128

I need somehow to go through every possible combination of times from 00:00 to 23:45 ( in 15 min increments ) as gthe times are changed in these cells it will change the values in the table in sheet 1.
WHat i need is for once every possible combination of times ( there are 96 different times ie 24 hours X 4 ) but as there are 128 cells (A1:A128) the possible combinations will be in the millions. In the table the best reuslt is simply the one which has as many cells in the table greater than zero.

Somehow I think this is something for a mathematicial genius rather than excel, as need sheet 2 to try every possible combination and give me the one which generates the best reult in sheet 1 table.

 

[xenou]

I'm afraid I'm not a mathematical genius. Brute force calculation seems like it would be much to slow. It seems to me we need to know more about what you are doing - what is the calculation being performed, why do they result in zeros or non-zeros, in what way is the information in table 1 dependent on this other sheet - and in short, is there any algorithm that could be devised to produce an answer by some means other than brute force. Actually, there are a few mathematical geniuses here so you may get lucky.

Alex

 

[mrod1942]

Sorry, but I am still not sure what you arelooking for. What is the nature of the contents if A1:A128? Are these times?

The cells in Sheet 2 are static?

You talk about combining them, but I don't see HOW you are combining them.

Tentatively, this looks like a job for the countif( ) function, but I can't be sure because I don;t know the nature of things in sheet 1, and I don;t know what you mean by "combining".

By the way, there are only 98*128 ways to combine 98 with 128 things. that's just under 128,000.

I will try to be more clear :
In the table in sheet 1 ( which is derived from another table plus what ever information is put into sheet 2 A1:A128

I need somehow to go through every possible combination of times from 00:00 to 23:45 ( in 15 min increments ) as gthe times are changed in these cells it will change the values in the table in sheet 1.
WHat i need is for once every possible combination of times ( there are 96 different times ie 24 hours X 4 ) but as there are 128 cells (A1:A128) the possible combinations will be in the millions. In the table the best reuslt is simply the one which has as many cells in the table greater than zero.

Somehow I think this is something for a mathematicial genius rather than excel, as need sheet 2 to try every possible combination and give me the one which generates the best reult in sheet 1 table.

 

[chaz_mac]

As I understand it, values in A1:A128 depend on value of time selected.

Being a linear thinker, I wonder if a not too fancy approach could work.

96 columns will fit from A to CR. It's not too hard to store the individual times in those columns.

Can you not then make the results in A1:A128 depend on the time in A129?

And then copy this set of formulas to the range B1:CR128?

Then a COUNTIF in A130:CR130 gives the number of positive values.

And a MATCH function can be used to tell you the column for which the greatest number af values are positive.

Make sense?

Chaz

 

[chaz_mac]

Sorry, I see that I misunderstood your original question.

Is the sequence of times always in order, or can it be random?

If random, then you truly do get a large number of combinations. I suspect that you are looking at a random start time, but all times after that increment by 15 minutes.

what is the case?

thanks -

Chaz