Spread annual fees across months in Excel

[ameador222]

I have a list of all customers that have had a transaction since Jan 2019 through Nov 2023. These customers are either on a 6 month or 12 month subscription plan, and their plans could have changed from 6 to 12 or 12 to 6 during that time. Keeping that in mind, I need to spread their payments into a monthly format in Excel. Customer name in the first column, then rows at the top of the sheet are months (Jan 2019, Feb 2019....). Right now, since we bill every 6 or 12 months, the data will show a payment in Jan 2019 for $100 and then nothing from February through December. I need a formula to change that to so that if they paid $100 in Jan 2019 for a 12 month subscription, it would show $8.33 per month from Jan 2019 through Dec 2019.

TYIA!

 

[felixstraube]

I'm supposing that the payments are in January if it is a 12 month payment and in January and July if it is every 6 month.
If that is the case then how about this?:

Libro1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI
1	C Name	ene-19	feb-19	mar-19	abr-19	may-19	jun-19	jul-19	ago-19	sep-19	oct-19	nov-19	dic-19	ene-20	feb-20	mar-20	abr-20	may-20	jun-20	jul-20	ago-20	sep-20	oct-20	nov-20	dic-20	ene-21	feb-21	mar-21	abr-21	may-21	jun-21	jul-21	ago-21	sep-21	oct-21	nov-21	dic-21	ene-22	feb-22	mar-22	abr-22	may-22	jun-22	jul-22	ago-22	sep-22	oct-22	nov-22	dic-22	ene-23	feb-23	mar-23	abr-23	may-23	jun-23	jul-23	ago-23	sep-23	oct-23	nov-23	dic-23
2	Name1	100												102												105												55						55						56						56
3	Name2	50						51						51						51						105												110												112
4
5
6
7	Name1	8,33333	8,33333	8,33333	8,33333	8,33333	8,33333	8,33333	8,33333	8,33333	8,33333	8,33333	8,33333	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,75	8,75	8,75	8,75	8,75	8,75	8,75	8,75	8,75	8,75	8,75	8,75	9,16667	9,16667	9,16667	9,16667	9,16667	9,16667	9,16667	9,16667	9,16667	9,16667	9,16667	9,16667	9,33333	9,33333	9,33333	9,33333	9,33333	9,33333	9,33333	9,33333	9,33333	9,33333	9,33333	9,33333
8	Name2	8,33333	8,33333	8,33333	8,33333	8,33333	8,33333	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,5	8,75	8,75	8,75	8,75	8,75	8,75	8,75	8,75	8,75	8,75	8,75	8,75	9,16667	9,16667	9,16667	9,16667	9,16667	9,16667	9,16667	9,16667	9,16667	9,16667	9,16667	9,16667	9,33333	9,33333	9,33333	9,33333	9,33333	9,33333	9,33333	9,33333	9,33333	9,33333	9,33333	9,33333
Hoja1

Cell Formulas
Range	Formula
A7:A8	A7	=A2
B7:BI8	B7	=LET( suscType,H2=0, p_1,IF(suscType,B2/12,B2/6), p_2,H2/6, IF(suscType,EXPAND(p_1,,12,p_1),HSTACK(EXPAND(p_1,,6,p_1),EXPAND(p_2,,6,p_2))) )
Dynamic array formulas.

If the payments are in other month or could be in any month, we would need some additional information on the payment period.