Calculating average of column if row meets criteria

**dvs** · 01-11-2015, 07:31 PM

I have a sheet with arrival and departure date and time.

I would like to calculate the following at 18:00 on each day:

Occupancy (This has been resolved)
Average length of stay of those currently staying
Number of guests staying 7 days or more

The attached file explains my request more clearly.

Thanks in advance.

DVS

**Ace_XL** · 01-12-2015, 06:17 AM

In J2
=IFERROR(AVERAGEIFS($C$2:$C$23,$A$2:$A$23,"<="&G2,$B$2:$B$23,">="&G2),0)

In K2
=COUNTIFS($A$2:$A$23,"<="&G2,$B$2:$B$23,">="&G2,$C$2:$C$23,">="&7)
or
=SUMPRODUCT(($A$2:$A$23<=G2)*($B$2:$B$23>=G2)*($C$2:$C$23>=7))

Also, no need for the double unary operators in Col H. Use
=SUMPRODUCT(($A$2:$A$3637<=G2)*($B$2:$B$3637>=G2))

**dvs** · 01-12-2015, 07:11 PM

Thanks for the quick response. I'm just testing it out with some data I was given. I seem to have an issue with room occupancy (it's lower than expected - think it's to do with time) and the average stay is higher than expected (but I think it's the data that I am using as it appear to work on a smaller set of data). Will just check with other data and report back. Cheers.