-1
  1. I have list of assets
  2. There is information about buy date and buy value
  3. there is information about depreciation year
  4. I want to make the year of each deprecation and the depreciation value, be described yearly from top to down automatically
  5. I prefer array formula to prevent me from dragging the formula every now and then

here is the link of the case: https://docs.google.com/spreadsheets/d/1wJ65_Q3bD1Fz_ueW2cmQP_L03r8U2jvbMXstzzjMDb8/edit#gid=0

yellow cells are raw data green cells are expected form of output

thankyou in advance!

player0
  • 124,011
  • 12
  • 67
  • 124
Randy Adikara
  • 357
  • 3
  • 10

1 Answers1

3

try:

=ARRAYFORMULA(SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY({(SPLIT(QUERY(FLATTEN(IFERROR(SPLIT(
 REPT(A2:A&"×♦"&TEXT(B2:B, "d mmm")&"×"&YEAR(B2:B)&"×♦"&D2:D/C2:C&"¤", C2:C), "¤"))), 
 "where Col1 is not null"), "×")), COUNTIFS(
 SPLIT(QUERY(FLATTEN(IFERROR(SPLIT(REPT(B2:B&"¤", C2:C), "¤"))), 
 "where Col1 is not null"), "×"), 
 SPLIT(QUERY(FLATTEN(IFERROR(SPLIT(REPT(B2:B&"¤", C2:C), "¤"))), 
 "where Col1 is not null"), "×"), SEQUENCE(SUM(C2:C)), "<="&SEQUENCE(SUM(C2:C)))}, 
 "select Col1,Col2,Col3+Col5,Col4 label Col3+Col5''")),,9^9)), "♦"))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124