Formula for percentage increase


#1

I would like to have a column for the % increase each week. For example:

On February 1 there were 50 people registered.
On February 8 there were 100 people registered.

How would I do this?


Struggling with Lookup
How to view upcoming project phase gates for portfolio of projects
Help with Formula
#2

My initial answer is:

(100-50)/50

As in, you calculate the increased % minus the original %, then divide by the original %. In this case, that’s a 100% increase.


#3

Thanks Alex - yes - trying to create a field that calculates this change automatically.


#4

If I knew your field names I could be more specific, but essentially you’d make a new formula field and replace the #s in my first response with the field names of the appropriate fields.

Good luck!


#5

Here’s what I’m trying to achieve:

43%20am


#6

Hmm, I see. That is a bit more complicated. Let me think about it for now and I will try and get back to you. The issue is you can’t pull the prior record’s total/increase data from the subsequent record without performing some other background calculations, or using another table to link the data to.


#7

What are you trying to accomplish with this data? Are there weekly or monthly metrics you want to know the % increase for?

I ask because I think there are a couple ways of going about this:

  1. Making records by week or month (and maybe year) which have subsequent columns for the weekly data (e.g., if the Record is ‘January’ you would have ‘Week 1’, ‘Week 2’ Fields/Columns, etc. for each month). Then you could calculate the increase from week to week, by month.

  2. You rearrange the entire table to have a single record ‘Increase’ for example, and each Field/Column is ‘1 January’, ‘8 January’ etc. – but this is likely more work than it’s worth.

  3. You create a Calculation table and link a single record in that table to every record in your existing table. That way you can aggregate the necessary data in the other table, and pull it back to your existing table (this get’s more complicated since your dates are text and not actual ‘Date’ field entries from which DAYTIME, etc. calculations could be derived)

At this point, once I (or the community here) can know a little more about what your end goal is, the solution will be easier to determine. But hopefully these ideas are helpful for the time being.


#8

That’s really helpful - thank you Alex. I’ll give these a go.

I really just want to update the raw registration numbers, and see the percentage change each week.


#9

Currently, there is no way directly to reference the value of a field in one record from another record, making such a calculation as

{'8 January'→Total} - {'1 January'→Total}

impossible.[1]

However, it is possible to reference such values indirectly, through a linked record.

Possible — but not very pretty.

With that caveat, here is a demo base that does what you requested. (Essentially, it implements version 2.0 of an algorithm I outlined in this earlier post.) Since it is based on linked records, it requires you to link each newly created record to a record in another table; however, as the second table contains only a single record, doing so adds only two mouse-clicks to the data entry process.

Note: The implementation shown in the demo base is very specific to the example you gave in that it is based on the cardinal week of the year, uses a text string in the format of “Day# MonthName” as the primary field, and so forth. However, it should be relatively easy to adapt the routines to support other methods of differentiating between current and previous measurements.

It’s probably best to start by looking at the base. (You’ll want to copy the base so you can examine and modify field configurations.)

You will see I have defined four new fields in [Table 1]{Link to Calc}, {FullDate},[2] {WeekCount}, and {PreviousWeek} — and a new table, [Calc]. (In use, {FullDate}, {WeekCount}, and {PreviousWeek} would typically be hidden.)

Upon creating a new record in [Table 1], three fields must be completed:

  1. {Date}. A string in the format “Day# MonthName” — “1 January”, “15 February”, and the like.

  2. {Total}. The total number of people registered as of that date.

  3. {Link to Calc}. A link to the [Calc] table.
    To create such a link, click on the plus sign ('+') in the {Link to Calc} field. Select the sole record in [Calc], named, in this case, '✅' — the Heavy White Check Mark emoji. This provides an immediate visual confirmation the record has been properly linked.

Once these three fields have been created, a value is defined for {WeekCount} consisting of the cardinal week of the year, formatted in double digits, followed by a colon (':'), and ending with {Total} — like so:

12:345

In the [Calc] table, the {Counts} field rolls up all {WeekCount} values into a single string, with the vertical bar character ('|') as separator. In addition, a vertical bar is prepended and appended to the concatenated string:

|01:20|02:23|03:36|04:63|05:118|06:394|07:437|08:475|09:490|

And, now, things get a little complicated. :wink:

In [Table 1], the {PreviousWeek} field in turn rolls up {Calc::Counts} using the following aggregation formula:

IF(
    AND(
        Date,
        Total
        ),
    VALUE(
        MID(
            ARRAYJOIN(values),
            FIND(
                '|'&
                    DATETIME_FORMAT(
                        DATEADD(
                            FullDate,
                            -7,
                            'days'
                            ),
                        'ww'
                        ),
                ARRAYJOIN(values)
                )+4,
            FIND(
                '|',
                ARRAYJOIN(values),
                FIND(
                    '|'&
                        DATETIME_FORMAT(
                            DATEADD(
                                FullDate,
                                -7,
                                'days'
                                ),
                            'ww'
                            ),
                    ARRAYJOIN(values)
                    )+1
                )-(FIND(
                    '|'&
                        DATETIME_FORMAT(
                            DATEADD(
                                FullDate,
                                -7,
                                'days'
                                ),
                            'ww'
                            ),
                    ARRAYJOIN(values)
                    )+4
                )
            )
        )
    )

In brief, that searches {Calc::Counts} for the {WeekCount} entry for the previous week and extracts the previous week’s {Total}.

Once {PreviousWeek} is determined, the calculations for {Increase} and {% Increase} are straightforward.

Well, I said it wasn’t pretty…

Actually, while it may look a bit daunting at first, the process is really quite simple; even better, by varying the method used to differentiate between current and previous values, it can be applied to a wide range of problems.

If any of this isn’t clear — ahem — please don’t hesitate to ask for further explanation!
. ----------

  1. Please note the representation I’m using here — {'1 January'→Total}, meaning "the value of {Total} in the record where the primary field is equal to '1 January'" — is of my own invention and completely invalid.
  2. {FullDate} simply converts such a string as "1 January" to an Airtable-standard Date field to conform to the record structure given in the original post; it is not a part of the current/previous calculation.

Vlookup Style Search in the Same Table
Help with Formula
Is there a way to create a moving average?