A Formula to track the % change every month.

Z.Win

I have a Smartsheet that tracks that status of positions within our department. I can't share it, since it contains personnel info, but the short version is, it has columns to indicate the division, if the position is vacant, who is currently in it if it's not vacant, etal. What i'm being asked to do it to track the % change every month. So if we have 10 vacancies at the end of March, and 9 vacancies at the end of April, i can tell them that the vacancies went down by 10% for that month.

My first thought was that i could use automation to copy the row that had the info when ever MONTH(TODAY()) = 1, so on the first of each month, it would copy to a log sheet.

Where I'm stuck is how to create a formula that will let me track the change from each previous month. I know i could make a column [Monthly change] and put a formula at row 2 something like =[current vacancy]@row - [current vacancy]1 and that would work for just that row, but how could i translate that into a column formula?

J Tech

Hi @Z.Win

To calculate the percentage change in vacancies from the previous month in a Smartsheet, you can use a combination of the INDEX and MATCH functions with a helper column to track the vacancies in the previous month.

Here are the steps you can follow:

Add a helper column to your sheet called "Previous Month Vacancies". In this column, you can use the following formula:

=IF(MONTH([Date Column]@row)<>MONTH([Date Column]@row-1),"",INDEX([Vacancy Column]:[Vacancy Column],MATCH(MAX([Date Column]1:[Date Column]@row-1),[Date Column]1:[Date Column]@row-1,0)))

This formula checks if the current row's date is in a different month than the previous row's date. If it is, it leaves the cell blank. If not, it uses the INDEX and MATCH functions to find the most recent vacancy count in the previous month.

In a new column, add the formula:

=IF([Previous Month Vacancies]@row="", "", ([Current Vacancies]@row-[Previous Month Vacancies]@row)/[Previous Month Vacancies]@row)

This formula calculates the percentage change in vacancies from the previous month. It first checks if the "Previous Month Vacancies" cell is blank, indicating that there is no previous month data. If it is blank, the formula returns an empty cell. If not, it calculates the percentage change by subtracting the previous month's vacancy count from the current month's vacancy count, dividing that difference by the previous month's vacancy count, and multiplying the result by 100 to get a percentage.

You can format this column as a percentage to make it more readable.

With these formulas in place, you should be able to track the percentage change in vacancies from month to month.

J Tech

Hi @Z.Win

To calculate the percentage change in vacancies from the previous month in a Smartsheet, you can use a combination of the INDEX and MATCH functions with a helper column to track the vacancies in the previous month.

Here are the steps you can follow:

Add a helper column to your sheet called "Previous Month Vacancies". In this column, you can use the following formula:

=IF(MONTH([Date Column]@row)<>MONTH([Date Column]@row-1),"",INDEX([Vacancy Column]:[Vacancy Column],MATCH(MAX([Date Column]1:[Date Column]@row-1),[Date Column]1:[Date Column]@row-1,0)))

This formula checks if the current row's date is in a different month than the previous row's date. If it is, it leaves the cell blank. If not, it uses the INDEX and MATCH functions to find the most recent vacancy count in the previous month.

In a new column, add the formula:

=IF([Previous Month Vacancies]@row="", "", ([Current Vacancies]@row-[Previous Month Vacancies]@row)/[Previous Month Vacancies]@row)

This formula calculates the percentage change in vacancies from the previous month. It first checks if the "Previous Month Vacancies" cell is blank, indicating that there is no previous month data. If it is blank, the formula returns an empty cell. If not, it calculates the percentage change by subtracting the previous month's vacancy count from the current month's vacancy count, dividing that difference by the previous month's vacancy count, and multiplying the result by 100 to get a percentage.

You can format this column as a percentage to make it more readable.

With these formulas in place, you should be able to track the percentage change in vacancies from month to month.

Z.Win

Hi @J Tech ,

I tried using what you sent me, but the first formula didn't work, but i managed to get some insight based on what you gave me combined with reading some threads on INDEX COLLECT and i got these formulas:

1) This is in a column called month index and it just tells me how many months since the first date

=IF(

  AND(YEAR([Date]@row) - YEAR(StartDate#) >= 1, MONTH(StartDate#) >= MONTH([Date]@row)), 

    ((YEAR([Date]@row) - YEAR(StartDate#)) * 12 - (MONTH(StartDate#) - MONTH([Date]@row))), 

      IF(

        AND(YEAR([Date]@row) - YEAR(StartDate#) >= 1, MONTH(StartDate#) < MONTH([Date]@row)), 

          (MONTH([Date]@row) - MONTH(StartDate#) + ((YEAR([Date]@row) - YEAR(StartDate#)) * 12)), 

            (MONTH([Date]@row) - MONTH(StartDate#))))

2) StartDate# is just =MIN(Date:Date) to determine the earliest date this reporting started

3) I used INDEX COLLECT to match not only the previous month, but also the same division

=IFERROR(INDEX(COLLECT([VACANT POSITIONS]:[VACANT POSITIONS], MonthIndex:MonthIndex, MonthIndex@row - 1, DIV:DIV, DIV@row), 1), "")

4) this is pretty much a copy paste of your % change

=IF([Previous Month Vacancies]@row = "", "", ([VACANT POSITIONS]@row - [Previous Month Vacancies]@row) / [Previous Month Vacancies]@row)

Thank you again for getting me started in the right direction.

zwin