Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup

Arrayformula to distribute ramp-up cost on other projects

I'm trying to solve this with an ARRAYFORMULA, but I'm sort of stuck, so I was hoping I could get some help here. So, I have this project calculation sheet, containing multiple customers and multiple projects. We have Ramp-up costs ("ToAllocate=TRUE", per customer) we need to distribute to other projects ("Carrier=TRUE", per customer). The Ramp-up costs (Cost per hour * Hours) need to be distributed to "Carrier" projects proportional to number of project hours / total of carrier hours. I need to come up with the arrayformula so I would have the column M filled in:

https://docs.google.com/spreadsheets/d/1OsWX80jvKgQAYuHi56ULBPm43GrZrnt7bvwtH5dy-ZM/edit?usp=sharing (columns J, K and L are helper columns so i wouldn't put everything in M column)

Solution

  • delete everything in J2:M range

    paste this formula into J2 cell:

    =ARRAYFORMULA(IF(D2:D=TRUE; {
       VLOOKUP(A2:A; QUERY(A:H; "select A,sum(H) where C=TRUE group by A"; 0); 2; 0)\
  F2:F/VLOOKUP(A2:A; QUERY(A:H; "select A,sum(F) where D=TRUE group by A"; 0); 2; 0)\
       VLOOKUP(A2:A; QUERY(A:H; "select A,sum(H) where C=TRUE group by A"; 0); 2; 0)*
 (F2:F/VLOOKUP(A2:A; QUERY(A:H; "select A,sum(F) where D=TRUE group by A"; 0); 2; 0))\
  I2:I+VLOOKUP(A2:A; QUERY(A:H; "select A,sum(H) where C=TRUE group by A"; 0); 2; 0)*
 (F2:F/VLOOKUP(A2:A; QUERY(A:H; "select A,sum(F) where D=TRUE group by A"; 0); 2; 0))}; ))

    0