Create Average based on date range

Hi All,

I have a tricky equation that is driving me mad.....please help!

Not sure if a pivot table would work or not.

The data is a finacial report based on hotel rooms and the average money each group of rooms generates.

I have three columns, one with a date, one with a room number, one with a monitary value.

I need to work out the average room revenue, by month, by group of rooms (1-22, 23-40)








Thanks in advance for any suggestions

Regards,

David

If you are running 2007 or later you can use the Averageifs formula. Take a look at the attached file to see if this is what you are needing to do. Remember this will not work if you are running 2003 or earlier.

thanks

Hi Tommy,

Thanks for a quick reply.

your formula worked for the first cell, but i cant see how it works for the rest of the table. I am clearly missing something obvious. Can you help please?

I have attached the sheet.

Thanks again

ignore last

View attachment LADY ANNE REVPAR.xlsx

sorry, i saw the mistake in the date lookup.

Does this look correct? I had to use $ and not sure if i got it right.

Thanks!

Hey Dave,

Everything looks good. I checked the math and it is averaging correctly . I did make a correction in the formula(s) on the room range . It was off on some of the formulas. like on Room 42-51 the formula was looking for >42 and <51 where it should be >41 and <52. I think you are good to go now.

Hi Tommy,

Thanks very much for your help - very much appreciated. Works a treat and saved me hours of potential manual work!!