Progressive totals working with column formulas.

andrew.taylor37161

Background: our business has Building Managers (BMs) that we provide to a multitude of our clients on a contracted hour/week basis.

The BMs have a fixed weekly work hour limit of 38 hours per week.

Our various clients have standard attendance hours per week that do not align with the weekly BM hour limit. This leaves us with capacity to use elsewhere.

We can, and do monitor this via rollups and various other methods (no help needed here)

We also have a healthy pipeline of upcoming business (and BM hour requirements) that is also logged in another sheet. We are trying to predict our resource requirements (BM hires) in the future so that we employ staff at the right time.

We can currently do this however the sheet is set up with a header row (to capture the current surplus, calculated via rollup in a summary field) and then we use a row formula on row 2 and drag it down to provide the progressive balance of available hours on new business that require BM hours because it is reliant on the previous row’s progressive balance.

On a regular basis we must swap the order as buildings get delayed etc and of course this breaks the formula, and we must add it back in to row 2 (as it is usually the next buildings coming on that are affected) and drag down.

This is very much an Excel type setup, and we of course cannot set the formula as a column formula.

If anyone has dealt with this before and resolved it to become a column formula, I would love to find out how it can be done.

Jgorsich

A lot of stuff that SEEMS like it can't be done as a column formula (due to needing a reference to a fixed cell or a summary formula) CAN be - if you are willing to become a bit inefficient in digital space usage…

For example, you are setting up your sheet currently to simply not allow for column formulas because you are trying to do everything in a single column. Split it into multiple columns though (and include an indexing column "Row" that just includes the integer values for the row - there are other posts on here that note how to do that), and it will likely become possible. So - right now you are using a summary field to get the current surplus - if you can, rewrite that into a new column titled "SummaryCurrentSurplus" so that you have a column that simply repeats that value over and over and over. Then add a column next to that titled something like "ProjectImpactOnBalance" where you have a formula that just tells you, regardless of the balance, what that particular row DOES to the bance (add/remove hours). Then add a 3rd column titled "ProgressiveBalance" that says "Take the value in SummaryCurrentSurplus and add all the values from "ProjectImpactOnBalance" that are from this row and earlier rows and show me the total here" - this would look something like "=SummaryCurrentSurplus@row+sum(collect(ProjectImpactOnBalance:ProjectImpactOnBalance,row:row,< = Row@row))"

then, wrap all of those formulas with something to protect against errors (my personal favorite is an if statement tied to a required column, like the building name, like this "=if(buildingname@row="","",insertRealFormulaHere)" so that you just get empty cells once the required column is empty).

Jgorsich

A lot of stuff that SEEMS like it can't be done as a column formula (due to needing a reference to a fixed cell or a summary formula) CAN be - if you are willing to become a bit inefficient in digital space usage…

For example, you are setting up your sheet currently to simply not allow for column formulas because you are trying to do everything in a single column. Split it into multiple columns though (and include an indexing column "Row" that just includes the integer values for the row - there are other posts on here that note how to do that), and it will likely become possible. So - right now you are using a summary field to get the current surplus - if you can, rewrite that into a new column titled "SummaryCurrentSurplus" so that you have a column that simply repeats that value over and over and over. Then add a column next to that titled something like "ProjectImpactOnBalance" where you have a formula that just tells you, regardless of the balance, what that particular row DOES to the bance (add/remove hours). Then add a 3rd column titled "ProgressiveBalance" that says "Take the value in SummaryCurrentSurplus and add all the values from "ProjectImpactOnBalance" that are from this row and earlier rows and show me the total here" - this would look something like "=SummaryCurrentSurplus@row+sum(collect(ProjectImpactOnBalance:ProjectImpactOnBalance,row:row,< = Row@row))"

then, wrap all of those formulas with something to protect against errors (my personal favorite is an if statement tied to a required column, like the building name, like this "=if(buildingname@row="","",insertRealFormulaHere)" so that you just get empty cells once the required column is empty).

andrew.taylor37161

Hi Jgorsich

Thanks for the guidance. I have used what you have suggested as the basis and got it to work.

Thanks greatly.