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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

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,698
Messages
5,573,689
Members
412,548
Latest member
wallisonlac
Top