Excel Forum

Hi Dear!
I have data in B2:H4 i need to generate allocation of prepaid expense base on amount from start date to end date.I need to generate even amount but only two dicimal so the last amount may be a bit different to adjust sum of the allocation equals to the base amount.
Previously ,I used this formula
=IF((B3<=EOMONTH(--$G$2:$R$2,-1)+1)*(C3>=$G$2:$R$2),ROUND(E3/D3,2),0)
i attached a template

Note I use excel 365


thanks

...

G3
=IF(COLUMNS($G3:G3)=MONTH($C3),$E3-ROUND($E3/$D3,2)*($D3-1),IF(COLUMNS($G3:G3)<MONTH($C3),IF(COLUMNS($G3:G3)<MONTH($B3),"",ROUND($E3/$D3,2)),""))

Copy to the right-hand side and copy down.

Bigcat9
RCA Bangkok Thailand

.......

...

For the spill array formula, you can try this.

G3
=LET(s,SEQUENCE(,12),IF(s=MONTH(C3:C4),E3:E4-ROUND(E3:E4/D3:D4,2)*(D3:D4-1),IF(s<MONTH(C3:C4),IF(s<MONTH(B3:B4),"",ROUND(E3:E4/D3:D4,2)),"")))

Bigcat9
RCA Bangkok Thailand

.......

Thx,will take a look

thank you for your nice solution.i come up with a formula =IF(
OR(
MONTH(DATEVALUE(G$2&" 1")) < MONTH($B3),
MONTH(DATEVALUE(G$2&" 1")) > MONTH($C3)
), "-",IF(MONTH(DATEVALUE(G$2&" 1")) = MONTH($C3),
ROUND($E3 - SUM($F3:F3), 2),
ROUND($E3/$D3, 2)
)
)

sna wrote: Mon Mar 04, 2024 5:45 pm thank you for your nice solution.i come up with a formula =IF(
OR(
MONTH(DATEVALUE(G$2&" 1")) < MONTH($B3),
MONTH(DATEVALUE(G$2&" 1")) > MONTH($C3)
), "-",IF(MONTH(DATEVALUE(G$2&" 1")) = MONTH($C3),
ROUND($E3 - SUM($F3:F3), 2),
ROUND($E3/$D3, 2)
)
)

Great!


Bigcat9
RCA Bangkok Thailand

Thank you for always help!