Re: Need to adjust formulas

Brandon_Pan · ‎Aug 01, 2018

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!

W_Vann_Hall · ‎Aug 01, 2018

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…

Brandon_Pan · ‎Aug 01, 2018

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.

W_Vann_Hall · ‎Aug 01, 2018

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.

Brandon_Pan · ‎Aug 03, 2018

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?

W_Vann_Hall · ‎Aug 04, 2018

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.