Results 1 to 6 of 6

Thread: Create Average based on date range

  1. #1

    Create Average based on date range

    Register for a FREE account, and/
    or Log in to avoid these ads!

    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)

    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



  2. #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.
    Attached Files Attached Files

  3. #3


    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
    Attached Files Attached Files

  4. #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.


  5. #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.
    Attached Files Attached Files

  6. #6
    Hi Tommy,

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts