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 ?

 

[Peter1973]

Any time can be random it does not go up in incremaent they all coulc be the same or 127 the same and 1 different or any combination, so any cell in the 128 could be an time from 00:00 to 23:45 ( as lon as it is divisible by 15 mins ) so 128 cell with any possible combination of 96 times, my view is the lottery is ony 6 balls with 49 numbers so more chance getting that than solving this.

Thanks to you all for putting thought into it.

 

[chaz_mac]

Then you are looking at 10^150 (roughly) combinations of time to apply to your matrix. I don't think you will live long enough if you do it in Excel.

If the dependence of your values on time could be characterized in mathematical formulas, my inclination would be to set up a Fortran (Just shows you how old I am ...) program to do this. I think it might increase the chances of surviving the run time, although you would probably find an extremely large number of time combinations that would give the same number of positive results.

If the number of positive numbers is equal, is there any value to having those numbers be a greater magnitude? (Just curious).

Chaz

 

[Peter1973]

Sourry to sound daft but could you explain ?

 

[PaddyD]

perhaps you can also give us more information on the problem you are actually trying to solve with this method, my thought being that any problem whose solution requires the explicit enumeration of millions of possibilities hasn't been appropriately specified - there might be an alternative approach.

 

[chaz_mac]

Let's say you have 10 bins you can fill with any of 10 things. Numerically this can be represented by a 10 digit number, whose range is from 0000000000 to 9999999999.

there are 10 raised to the 10th power of these, or 10 billion.

you can fill 128 bins with any of 96 numbers, so that, i believe, is represented by 128 raised to the 96 power. In decimal terms, 1.96 times 10 to the 202 power.

that is a lot of combinations to try.

Furthermore, your population of numbers that can take positive or negative values is numerically not very big, so there are not that many combinations of results that can occur for your huge number of tests. So many duplicate results, in terms of number of positive numbers, must occur.

I wondered, as a point of curiosity, whether it made any differences if numbers were "more positive" if there were an equal count of positive numbers.

I hope this helps -

Chaz

 

[Peter1973]

It does not matter if the number is +10 or +1 it is the total count of positive througout that counts.

Paddy I will try to explain my probles I have a table of times which is 7 acros and 96 down. The 7 across represents day of the week and the 96 down represent the 24 hour clock broke down in 15 min increments ( so table would be in B1:H97 with days of week in B1:H1 and times in A2:A97 ie 00:00, 00:15, 00:30 and so on ) The in each of the cells will be anumber so a 2 in a cell would mean you need 2 at the time in that row on the day in that column ) You would need 2 of ( the next table will explain )

Then I have another table 128 down and 7 across ( across represents days of week and the 128 down is the total numbers of times allowed can be any time, except in each row across only 5 of the cells will be active ( various combinations which are fixed )

In the 2nd table a row must contain the same time. I am trying to find the best combination of times which will look in table 1 for all the times required on different days at all the times then look in table 2 and count how many times there are ( that match both day and time ) and return the difference.

SO if table 1 said I needed 2 03:15 on Sunday and table 2 said I had 3 93:15 on sunday this would return a +1 ( would assume in a 3rd table same dimensions as table 1 ) The more positive numbers in table 3 the better so looking to get the best combination possible in table 2.

Hope this is clear as it hurt my head.

 

[mrod1942]

I still don't know what is the nature of the contents of sheet 1. Again, are they also times? You can't build anything without knowing the data type for the purpose of building a comparison. Perhaps you could send me a sample workbook? If you are willing to send, you can use my private email mrodrigues@cox.net

***********************************

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.

 

[Peter1973]

Table 1 has numbers in this is how many are required at the time that row represents on the day that column represents, have sent sample to your e-mail hope you can explain as im having a hard time with this.

Prehaps if I explained it is a rota sytem so table i is how manypeople I need at certain times on each day, Table 2 will be what the people are working ie which days ( this is fixed cannot be changed ) all I can chnage is peoples start time so need the best combination of start times for 128 people to meet my demands of table 1.

 

[Fazza]

I'm not surprised that this is proving elusive when there is such a lack of clarity!

Peter, can you simply find the one cell that has the maximum characteristics and then select that entry 128 times?

 

[Peter1973]

I suppose because I know what I need it seems to make sense to me.

Basically I have a table ( table 1 which tells me how many people I need to start work at any time of the day will always be either on the hour quater past, half past or quater too ) This table simply has times down one side and days of the wek above so 24 hour clock divsible by 4 as stated above ie half past, quarter too etc = 96 rows and days of week = 7 columns. Each cell will contain a number whic when you match x and y axis will tell you how many people you need to start work at a particular time on any day.

Table 2 details what the people actaully work ( 5 days out of 7 which cannot be changed ) there are 128 colleagues in total and the only flexability I have is to change thier start time. So in essence table 3 would look in table 2 count how many people i have starting work at say 03:00 on Tue the see from table 1 how many I need and give me the difference. As it is better for me to have as many posiitive numbers ie more staff than required I am looking for the best combination of start times for table 2.