Formula to Calculate Percentage Change from Baseline

wispsofsmoke

Hi,

We capture scores between 1-5 each month. I'm looking to create a formula to calculate the percentage change from the baseline based on the most recent month's entry in that row.

For example, baseline (starting) score was 3 in June (captured in the column "June"). Let's say it's now August and the score is 4 (captured in the column "August"), so I want a "Percentage Change" column to show 33%, representing the change from June-August. When a score is entered for September, I want the "Percentage Change" column to automatically show the percentage change between June-September.

Thanks in advance!

ChristianFinke

@wispsofsmoke

To calculate the percentage change from the baseline, use a formula like =IF([June]@row = 0, 0, ([Last Column Name]@row - [June]@row) / [June]@row * 100), replacing [Last Column Name] with the most recent month’s column. If columns change often, a helper column could track the latest score dynamically.

wispsofsmoke

Thanks Christian!

Is there a way to have it scan for the most recent column automatically so I don't have to update the month name?

For example, I want to have placeholder columns for each month per year vs. add new columns each month and then have the "Percentage Change" column at the end (to the right, after December). So, let's say I already have columns in 2024 for June through December. If I enter a score today for November, it would calculate the change from June-November, but the December column would be blank. When I enter a score in the December column, it would then update for June-December because there is now data in that column.

Is that possible? I know I've done it in Excel but don't remember how.