Arrayformula to distribute ramp-up cost on other projects

1
votes

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)

1
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup
so you say you dont need JKL columns? – player0
I need the results in M column. But instead of putting everything into 1 cell, I thought it would be easier to understand the steps if I split the formula into multiple steps. So the 3 columns are steps for achieving the result in M. But I need to have the calculation in array formula. – Mara

1 Answers

1
votes

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