Hello!

I am having problems with my formulas as there are NaN’s showing up in the column so I can’t see the sum/average of the column at the bottom. I will go into detail the formulas/information that I am trying to collect below.

There are formulas that are collecting monthly occupancy and ADR. So for example, 2016-12 Occupancy has the formula “IF({2016-12 Total Occupied Days}/31 * 100 = 0, BLANK(), IF({2016-12 Total Occupied Days}/31 * 100 > 100, 100, {2016-12 Total Occupied Days}/31 * 100))”. I have the IF formula so that any units that do have a value of 0 won’t skew the sum/average across the portfolio.

The 2016-12 ADR formula is “IF({2016-12 Total Occupied Days} = 0, BLANK(), {2016-12 Total Payout}/{2016-12 Total Occupied Days}/{Bedrooms - Filtered})”.

Right now, my 2016 Year to Date Occupancy column has a formula that’s simply taking the average of all the monthly occupancy columns: “AVERAGE({2016-04 Occupancy},{2016-05 Occupancy},{2016-06 Occupancy},{2016-07 Occupancy},{2016-08 Occupancy},{2016-09 Occupancy},{2016-10 Occupancy},{2016-11 Occupancy},{2016-12 Occupancy})”. I would think the problem stems because some of these columns are blank, as there is no value there. So for example, 2016-10 Occupancy, 2016-11 Occupancy are blank.

It’s the same issue for ADR as well. Some monthly ADR columns are blank because we simply did not have it available for that month. The ADR 2016 YTD formula is just: “AVERAGE({2016-05 ADR},{2016-06 ADR},{2016-07 ADR},{2016-08 ADR},{2016-09 ADR},{2016-10 ADR},{2016-11 ADR},{2016-12 ADR})”.

Any help would be much appreciated. Thanks in advance!