Excel - What is the easiest way to calculate incidence plus prevalence over time?

Question

Say I have the dataset below, what is the most efficient formula to fill the cells in column D, where the number of patients alive are calculated?

Example data set in excel

The way it should calculate is:

month 1: 8*100% = 8

Month 2: 8*80%+6*100% = 12.4

Month 3: 8*75%+6*80%+9*100% = 19.8

...

Month 10: etc.

The problem that I have is that which each row, the formula becomes longer. It is feasible to just manually enter the formulas for small datasets, but as datasets become larger, this task becomes unfeasible.

I have been able to use VBA to code the survival of the number of new patients column (C). But then I would have to rerun the VBA code as soon as I change a single value in that column.

I have a feeling it should be possible with some combination of the INDEX function in excel, I just haven't been able to figure it out.

Who can help me out here?

Kind regards, Sander

Answer 1

If moving the data a bit is allowed at least for the calculation, you could do something like this:

=SUMPRODUCT($F$11:$F$20,B2:B11)

It uses a reversed list of your current list of new patients. That list is created with (formula obtained from this site):

=INDEX($C$11:$C$20,COUNTA($C$11:$C$20)+ROW($C$11:$C$20)-ROW())

Result:

The added space is necessary for the formula to work (so that it gets 0% for patients not present yet).

---

Or one where you don't have to leave spaces (everything from above is reversed however):

=SUMPRODUCT($C$2:$C$11,G11:G20)