Keeping the higher value in a variable row

Copper Contributor

I have a challenge that seemed simple to me at the beginning. Famous last words. Hoping you can provide guidance. Some context before the question if that helps to frame it better.

Context:

A row is calculating the number of floors in a building that need to be built out in order to accommodate temporary staff. When the staff number declines for some number of months, we still keep the floor and fill it as staff are hired again when needed. The columns are months over a 5 year period. Note that at the end of each 12 month period, there is a sum column to show total for that fiscal year.

Question:

A row calculates a number that may increase or decrease for each column, such as...

1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 1, 1, SUM COLUMN,1, 1, 1, 1, 2, 2, 2, 3, 3, 3, 3, 3, SUM COLUMN, 3, 4, 4, 4, 3, 3, 3, 3, 4, 4, 4 etc.

The pattern is variable depending on other inputs. However, we need to keep the higher number once it appears. Using the example above, we would need to have it show the following (or have it appear on another row that we can use for our next set of calculations):

1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2, SUM COLUMN, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 3, 3, SUM COLUMN, 3, 4, 4, 4, 4, 4, 4, 4, 4, 4, 4 etc.

Because the pattern is variable, I can't come up with a way to maintain the higher number once it appears.

Thank you for your help!

 

5 Replies

@morryp 

The dynamic MAX formula in B3 is: 

=MAX(INDEX(2:2,2+13*INT(COLUMN()/14)):
INDEX(2:2,COLUMN()))

The foregoing formula is then copied across, skipping the sum column (in bold format), as partly shown below: 

Dynamic Max-1.PNG

@Twifoo Thank you very much for the formula. It works beautifully when I test it on a blank sheet recreating the three rows and applying the formula in B3 as you explained.

 

When working on the live model I inserted a row below the variable numbered row and applied the formula. It would not return a value. Is the formula dependent on the row and cell? 

In that case, lock the “Required” row. In MY SAMPLE (because you didn’t attach any), modify 2:2 to $2:$2.

Hi @Twifoo 

 

Perfection!. Thank you so much for your solution. I greatly appreciate it.

Best regards

You’re perfectly welcome!