Forum Discussion
Formula/function to evenly distribute revenue over a variable number of months??
Hi, in a similar situation. Trying to figure out a single dynamic formula that will allow me to split up the values from Column A (say a revenue contract value) across columns B-Z with various start and end dates. like this
Contract Value Start Date End Date Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20 $1,100,000 09/06/20 09/06/23 $50,000 09/06/20 NA $375,000 05/28/20 05/28/21 $172,475 02/03/21 02/03/22 John_Bloggs650 This is slightly different from the original post. Best to start a new thread.
Anyway, which Excel version are you on and how would you split the examples if you had to do it manually? For instance, the first one covers three years (i.e. 36 months) but you mention to want to split across columns B:Z (25 columns). And the second one. How would you threat a contract with no end date?
Just started one here: https://techcommunity.microsoft.com/t5/excel/dynamic-formula-that-splits-distributes-revenue-a-value-among/m-p/3639621#M165143
Im using Office 365. sorry the columns would be A: CA.Have been playing around with this formula but the days in each month are variable so 30 doesn't necessarily work, with the total falling slightly short.
=($E2<=H$1)*(H$1<=$F2)*($D2/(($F2-$E2)/30))
Trying to do something similar to this:
https://miro.medium.com/max/1400/0*6-oBlRyn-GVQcAPq.png
Riny_van_Eekelen Yes- that is exactly what I was looking for. I can't thank you enough!
