# Thread: Create Average based on date range

1. ## Create Average based on date range

Hi All,

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)

 Date rm no revenue oct-11 2 45 oct-11 4 76 nov-11 42 45

 oct -11 nov -11 dec-11 room 1-21 ?average ?average ?average room 22-40 ?average ?average ?average

Thanks in advance for any suggestions

Regards,

David

2. 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.

3. ## thanks

Hi Tommy,

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

4. ## ignore last

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!

5. 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.

6. Hi Tommy,

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