Referencing a cell value in a formula

1

Hi everyone:

Can you please help me figure out how to reference a value in a specific cell in a formula.

Essentially, I’m trying to set up the following formula:

[Revenue in Current Month]= [Revenue from Previous Month][1+ Growth Rate].*

My tables look like this:

image
image1176×768 14.6 KB

I have the percentage growth for revenue in a separate table, so I can make my formula dynamic. I’ve been trying to achieve this cell level reference through the lookup function but I get stuck on the third argument:

image
image1525×848 40.9 KB

Am I doing this correctly? Is there a simpler approach?

Thank you
Vic

2

The formula reference recommends using Filter() instead of Lookup() but they work somewhat similarly. This should get you where you’re going:

[Jan] * (
    1 +
    [Growth Rates].Filter(
        currentValue.Category = thisRow.Category
    ).First().Rate
)

You can have this all on one line, or broken up like I’ve done here for clarity (makes no difference).

  • This takes January and multiplies it by 1 + …
  • The growth rates table, filtered just to items where the category matches.
  • You don’t have to explicitly write out currentValue and thisRow, but it can help. The alternative to writing them out is to type Category and then hover on the suggested options - Coda will tell you which table’s “Category” column each one belongs to. “CurrentValue” in Coda means the current row in Growth Rates table being considered by the filter, and “thisRow” means the row in P&L table where this formula lives.
  • Then we take the first result, just in case you had multiple “Revenue” rows in Growth Rates (things would get weird if we had >1 result)
  • Then once we have our row selected, that’s when we tell it we’re interested in the Rate column

Edit to add: Coda gives you the option of dot chaining instead of bracket wrapping. More details here. Essentially, to grab the first item, you can either do the Excel-style First(a list of items) or the more chronological-and-therefore-intuitive-to-many (a list of items).First()

If you wanted to re-write the above fully wrapped it would be more like

[Jan] * (
    1 +
    First(
        Filter(
            [Growth Rates],
            currentValue.Category = thisRow.Category
        )
    ).Rate
)

But personally I think you’ll have an easier time if you get into the dot chaining headspace, because it matches Coda structures like thisRow.aColumn.anItemInThatColumn.ThatItemsProperties

2 Likes
3

Great constructive feedback @Nick_HE, much appreciated :pray: Always nice to see the logic explained :slightly_smiling_face:

4

Thank you, so much @Nick_HE ! I’ll give this a try. I appreciate your help!