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?


How to view upcoming project phase gates for portfolio of projects
Struggling with Lookup
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


Calculating Percent Change
#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
Is there a way to create a moving average?
Can't figure out how to do a simple percentage calculation in Airtable
Help with Formula
#10

It’s a shame this doesn’t exist in a simple formula as you could do in Excel. I.e. in excel you just do A2/A1*100 for example, where you are looking at the % growth from A1 to A2. Then copy that down as you add new rows. Or just use the GROWTH formula.

I want to do similar to Steven, in that when I add new data in each week, I want to see immediately how it compares with the previous week’s data that I had already entered.

In Airtable I cannot see a simple way of achieving this same outcome. Would be great to implement excel’s GROWTH formula please.


#11

Hi Brett

This isn’t really possible with a database because the order of records is not set in stone (you could sort, filter, group, delete etc). It would probably be possible to use Integromat to loop through your records and update the data periodically though - but this may be an overkill!


#12

I want to put in the Trailing Stop for an amount as in stocks and I thought you might be able to help. $15.00 with a trailing stop of 25%. In Excel I put in: =The column*0.07. So in other words when the stock hits 25% more than I paid, which is the $15.00, I want that answer. But I can’t figure out how to do this in Airtable. Can you help?