Create Average based on date range

davebarton

New member
Joined
Oct 20, 2012
Messages
4
Reaction score
0
Points
0
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)



Daterm norevenue
oct-11245
oct-11476
nov-114245


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



Thanks in advance for any suggestions

Regards,

David
 

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
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
Joined
Oct 20, 2012
Messages
4
Reaction score
0
Points
0
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
 

Attachments

  • LADY ANNE REVPAR.xlsx
    280.5 KB · Views: 20

tommyt61

New member
Joined
Mar 26, 2011
Messages
153
Reaction score
0
Points
0
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.
 

Attachments

  • LADY ANNE REVPAR CORRECTED.xlsx
    274.4 KB · Views: 29

davebarton

New member
Joined
Oct 20, 2012
Messages
4
Reaction score
0
Points
0
Hi Tommy,

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