Search code examples
excelformula

How to ensure list of numbers in excel column continues from the last non zero number

Imagine 3 columns, Column A and Column B and Column C. Column A is a date column. Column C is lookup column that looks up a value from 1 - 100 from a different table based on the date. Column B is continuation of values from 0 from the first date value and continues with the value until the value in column c changes.

How do you formulate column B so it automatically keeps the last non zero value until another value in column C appears?

Column A Column B Column C
01/01/2021 0 0
02/01/2021 0 0
03/01/2021 0 0
04/01/2021 20 20
05/01/2021 20 0
06/01/2021 20 0
07/01/2021 50 50
08/01/2021 50 0
09/01/2021 50 0
Solution

  • Either

    =IFERROR(LOOKUP(2,1/(C$1:C1<>0),C$1:C1),0)

    Or;

    =XLOOKUP(TRUE,C$1:C1<>0,C$1:C1,0,0,-1)

    Or, if these values are always bigger then the last one:

    =MAX(C$1:C1)