# Create Average based on date range

#### davebarton

##### New member
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

#### tommyt61

##### New member
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.

#### Attachments

• Revenue_Average.xlsx
9 KB · Views: 26

#### davebarton

##### New member
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

#### Attachments

280.5 KB · Views: 20

#### tommyt61

##### New member
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.