CODEWITHSUNDEEP
arraysgoogle-sheetspivot-table
roundrobin
roundrobin

Reputation: 716

Pivot table - Display growth rate with calculated field in Google Sheets

I'm trying to create a calculated field that displays the growth rate in % from one of the numeric columns in my pivot table.

How can I reference a row above the current cell to calculate the growth rate?

=(Current cell - (Current cell - 1 Row)) / (Current cell - 1 Row)

Pivot table

Year Count Calculated Field (YoY Growth)
2019 100
2020 300 100%
2021 600 50%

enter image description here

Upvotes: 5

Views: 2320

Answers (2)

player0
player0

Reputation: 1

based on instructions:

=(Current cell - (Current cell - 1 Row)) / Current cell

={""; ARRAYFORMULA(IFERROR(B2:B-(B2:B-B1:B999)/B2:B))}

enter image description here

Upvotes: 1

Erik Tyler
Erik Tyler

Reputation: 9345

I agree with player0, that seeing your sheet, data, data types and layout would facilitate helping you.

However, I'll venture a solution, sight unseen.

There are actually many ways to address what I think you're asking. This is just one of them.

Suppose that your source numbers are in range A2:A. Place the following formula in some other open column, in the cell parallel to the top number in your source range:

=ArrayFormula(ARRAY_CONSTRAIN({"";FILTER(({A3:A;""}-A2:A)/{A3:A;""},A2:A<>"")},COUNTA(A2:A),1))

I've added in more "parts" than are probably necessary. But I tried to include controls, seeing as I don't know what your data actually looks like.

An alternative is to place the following simpler formula into the cell one below where the top number in your source range is:

=ArrayFormula(IF(A3:A="",,(A3:A - A2:A)/A3:A))

In either case, you'll want to format the column/range where the results will be as percent (Format > Number > Percent).

Obviously, if your source numbers are in a range other than A2:A, you'll need to adjust the ranges in either formula accordingly.

Upvotes: 1

Related Questions