# Is excel capable

#### Peter1973

##### Well-known Member
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 ?

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.

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?

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 (-).

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

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.

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

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.

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

Last edited:
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

Replies
5
Views
444
Replies
3
Views
250
Replies
10
Views
690
Replies
3
Views
243
Replies
0
Views
164

1,196,429
Messages
6,015,207
Members
441,882
Latest member
rcgyuk

### 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.

### Which adblocker are you using?

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

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