Time vs Rate/value

[Grommit07]

I'm not sure that this can be done.

I have a spreadsheet with 2 work books, one is where the data is entered, and the second is where the data is to be calculated from. In the first work book, if was to enter "start time"06:00 and "end time" 09:00, I need it to select the values in between those times on a specific day in the second worksheet, and give me an average rate, and then for that rate to then appear in the rate coloumn in the first workbook.

Can this be done?

[Pete_UK]

Some confusion over terminology here - you have two workSHEETS within one workBOOK, as a workbook is an Excel file.

However, I'm not sure where you would enter the start time and the end time in the Rate Card sheet, or where you would expect the result of any calculation to appear in the Booking Summary sheet. Can you be a bit more explicit, by providing a few examples?

Pete

[Grommit07]

Sorry, I'm still trying to get my head around this also.

I have attached an updated booking summary.

What I would like to do is, under "start time"" in the first worksheet, is enter a start time, for example 09:00, and then under "end time" enter the end time, for example 10:30. Say I was trying to book a commercial to run between those times. So I would have a start time and end time of when the commercial could play.

And from entering that data it would then select the "rates" from the second worksheet from between those times. So from across the 7 days from 09:00 to 10:30 it would calculate the average from between those time zones and then give me a result in the first worksheet under the rate column.

And then further on in the first work sheet, if you where to enter a number, such as "2" under the day you would like to book a commercial, the formulas we have created in start time and end time would then only select the day we have entered and calculate the rates between 09:00 to 10:30 on for example, wednesday.

booking sheet template.xlsx

[Pete_UK]

You have a lot of cells formatted as Text, including the times in your lookup table. I'll have to clear your file up a bit before I can work with it.

If your end time is 10:30, do you want to include that row in the average rate, or just the rows for 9:00, 9:30, and 10:00 ?

Pete

[Pete_UK]

Okay, I've tidied up your workbook (attached). This formula in E12 gives the average rate for the period chosen:

=IF(OR(B12="",C12=""),0,AVERAGE(INDEX('Rate Card'!$B$2:$H$61,MATCH(B12,'Rate Card'!$A$2:$A$61,0),):INDEX('Rate Card'!$B$2:$H$61,MATCH(C12,'Rate Card'!$A$2:$A$61),)))

This is then copied down. I've also put a formula in the duration column.

I'm not sure what you mean in your last statement. Are you not making use of the average rate? I think you would need another block of 32 columns further to the right to calculate the cost for each time-slot booked for each day.

Hope this helps.

Pete