Time vs Rate/value

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