Adding distribution to data table with cumulative sum

plantaddict

New Member
Joined
Aug 12, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Hi, I would really appreciate some urgent help on this please as I'm so stuck.

I currently have a few rows of data and I'm looking to shift the data by a month, apply some distribution probabilities based on how many months have elapsed and sum cumulatively.
Say I have a distribution table with 4 probabilities being: t=1 is 40%, t=2 is 30%, t=3 is 20% and t = 4 is 10%.

Data is shown below:
Oct 20Nov 20Dec 20Jan 21Feb 21Mar 21
20302540
2555507080
35457550

I'm looking for a formula that would automate the following:
Oct 20Nov 20Dec 20Jan 21Feb 21Mar 21
=20*40%=(20*30%)+(30*40%)=(20*20%)+(30*30%)+(25*40%)=(20*10%)+(30*20%)+(25*30%)+(40*40%)=(30*10%)+(25*20%)+(40*30%)
=25*40%=(25*30%)+(55*40%)=(25*20%)+(55*30%)+(50*40%)=(25*10%)+(55*20%)+(50*30%)+(70*40%)=(55*10%)+(50*20%)+(70*30%)+(80*40%)
=35*40%=(35*30%)+(45*40%)=(35*20%)+(45*30%)+(75*40%)=(35*10%)+(45*20%)+(75*30%)+(50*40%)
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

plantaddict

New Member
Joined
Aug 12, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
Any help would be very much appreciated! Thank you so much
 

Watch MrExcel Video

Forum statistics

Threads
1,118,792
Messages
5,574,319
Members
412,587
Latest member
Krucial155
Top