Excel Help

[krono9]

Hi, I was wondering if someone could help me with the following spreadsheet.

Source: https://dl.dropbox.com/u/26021821/Excel/Optimal Periods.xlsx


Column A = a simple reference to that line/record.
Column B/C = start dates and end dates for that record event.
Column D = Performance value for that specific record for that period.

Is it possible using excel to find the optimal mix of records/rows to cover all the days of a year (say 2012) with(using) the maximum total "performance value"..

Almost like sorting by column D from big to small and allocating the available days from top to bottom... but record/row periods cannot overlap....

Does this make sense? Can someone help?

Thanks.

 

[InvisibleJim]

Not sure I understand your problem but maybe look at Excel's solver add in.

Edit: I can't find any info on whether solver can work with dates or date ranges as variables

 

[Tim the Techxpert]

Hi There,
I do understand your problem or at least I think I do so let me explain what I believe you want to do and maybe then some other people can suggest solutions other than mine.

You want to be able to look up through the table and cover the entire year with the least cost . So to understand a bit more I sorted your data into start date sequence. Straight away I could see overlapping records for instance 7th march and 19th March where one is less than the other.
Scenario and goal seek is not going to solve this one for you I don't believe.

I think you should try writing a script in visual basic to do it. VB is not my forte so I am not going to volunteer that.
You could look at doing some macro writing and date comparisons to see in the start date of one entry falls within the range of another entry and then set flags as to which one you want to use.

Hope that helps

Tim