I’d start by looking at the formula that reads
IF(
{2016-12 Total Occupied Days} = 0,
BLANK(),
{2016-12 Total Payout}/{2016-12 Total Occupied Days}/{Bedrooms - Filtered}
)
While you’re trapping for an instance when {2016-12 Total Occupied Days} is equal to zero, that may not catch instances when {2016-12 Total Occupied Days} is blank. I think the following will bypass both blank and zero values:
IF(
{2016-12 Total Occupied Days},
{2016-12 Total Payout}/{2016-12 Total Occupied Days}/{Bedrooms - Filtered}
)
(You can explicitly state the ‘else’ portion of that statement by adding BLANK(), but it’s, not needed.)
You may also want to make sure there are no cases when {Bedrooms - Filtered} is either zero or blank…
I’d start by looking at the formula that reads
IF(
{2016-12 Total Occupied Days} = 0,
BLANK(),
{2016-12 Total Payout}/{2016-12 Total Occupied Days}/{Bedrooms - Filtered}
)
While you’re trapping for an instance when {2016-12 Total Occupied Days} is equal to zero, that may not catch instances when {2016-12 Total Occupied Days} is blank. I think the following will bypass both blank and zero values:
IF(
{2016-12 Total Occupied Days},
{2016-12 Total Payout}/{2016-12 Total Occupied Days}/{Bedrooms - Filtered}
)
(You can explicitly state the ‘else’ portion of that statement by adding BLANK(), but it’s, not needed.)
You may also want to make sure there are no cases when {Bedrooms - Filtered} is either zero or blank…
So I went with your suggestion. But the problem is still the column that averages the data for the year. In this case, it would be “2017 YTD Occupancy”, which just takes the average of all the monthly occupancies. However, there are some units that weren’t active in 2017, so there are no values for any of the months. These are the units that will have NaN in the YTD column. Right now, the formula is just the average of all the monthly occupancy columns. Is there something that I can include to get rid of NaN for entries that have no entry.
So I went with your suggestion. But the problem is still the column that averages the data for the year. In this case, it would be “2017 YTD Occupancy”, which just takes the average of all the monthly occupancies. However, there are some units that weren’t active in 2017, so there are no values for any of the months. These are the units that will have NaN in the YTD column. Right now, the formula is just the average of all the monthly occupancy columns. Is there something that I can include to get rid of NaN for entries that have no entry.
This should do it (adjust the monthly entries as needed):
IF(
NOT(
ISERROR(
AVERAGE(
{2017-01 ADR},
{2017-02 ADR},
{2017-03 ADR},
{2017-04 ADR},
{2017-05 ADR},
{2017-06 ADR},
{2017-07 ADR},
{2017-08 ADR},
{2017-09 ADR},
{2017-10 ADR},
{2017-11 ADR},
{2017-12 ADR}
)
)
),
AVERAGE(
{2017-01 ADR},
{2017-02 ADR},
{2017-03 ADR},
{2017-04 ADR},
{2017-05 ADR},
{2017-06 ADR},
{2017-07 ADR},
{2017-08 ADR},
{2017-09 ADR},
{2017-10 ADR},
{2017-11 ADR},
{2017-12 ADR}
)
)
Note, though, AVERAGE() in this case may not yield the result you want, as it returns the average of non-blank entries. Say you have an entry for only October, 2017, with a value of 5: the AVERAGE() statement will return 5. That may or may not be correct, depending upon your application.
This was just what I needed. Thank you! I don’t know too much about airtable formulas - is this IF(NOT(ISERROR formula something I can use whenever I get NaN results?
This was just what I needed. Thank you! I don’t know too much about airtable formulas - is this IF(NOT(ISERROR formula something I can use whenever I get NaN results?
Beats me — I was kind of surprised it worked. (I seem to recall having had problems trapping what I thought should be an obvious error. That said, it was not long after I started using Airtable, and the problem may have been that Airtable didn’t generate an error when I expected it to; For instance, see VALUE('89Tr32').) I think it should trap any #ERROR! or NaN result.
