• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Cost Split based on Allocation and FTE

fareedexcel

Member
Hello,

I need to split the cost between different Sector (Alpha, Beta, Gamma, Hexa) and I have the employee FTE and Cost. In the sheet 4 different scenarios are mentioned and I need a formula which can support all the 4 scenario conditions.

Please help.
 

Attachments

  • Formula for Cost Split.xlsx
    13.2 KB · Views: 3
Try.

In "Answer" N9, formula copied across right to AC9, and all copied down:

=INDEX($F9:$I9,INT((COLUMN(A$1)-1)/4)+1)*INDEX($J9:$M9,MOD(COLUMN(A$1)-1,4)+1)

83131
 
Thanks @bosco_yip , but in row 10, i have only 1 allocation which is 70% and the full cost 95,909 should go to Alpha with the same FTE 0.70 as George have 2 cost centers.
 
Try, to revise as per highlighted,

In "Answer" N9, formula copied across right to AC9, and all copied down:

=INDEX($F9:$I9,INT((COLUMN(A$1)-1)/4)+1)*INDEX($J9:$M9,MOD(COLUMN(A$1)-1,4)+1)/SUM($F9:$I9)

or, this In N9, copied down:

=INDEX(F9:I9,INT((SEQUENCE(1,16)-1)/4)+1)*INDEX(J9:M9,MOD(SEQUENCE(1,16)-1,4)+1)/SUM(F9:I9)

83135
 
Last edited:
Thanks for the result. May I also ask you if there are more addition allocation columns, let's say instead of 4 (Alpha to Hexa) there are 7 columns, then how to reframe the formula.
 
Thanks for the result. May I also ask you if there are more addition allocation columns, let's say instead of 4 (Alpha to Hexa) there are 7 columns, then how to reframe the formula.
In order to avoid misunderstanding, please open a new post for your new question.

Thank you.
 
I have no reason to believe this is relevant to the OP. However it may be of relevance to users of Excel 365 and highlights both shortcomings of Lambda helper functions and a possible workaround.

The first step to apportioning costs across sectors is to ensure the percentage split for each row sums to 100% and is not influence by other rows in the same scenario. For that I wrote a problem-specific Lambda function Normalise
Code:
Normaliseλ(percent)
= LAMBDA(percent,
    percent / BYROW(percent, SUMλ)
  )
 
  where
 
  SUMλ(x)
  = LAMBDA(x, SUM(x))
which divides each row by the sum across the row.

The tricky part is taking each column of the normalised percentages and array multiplying them into the range of overall FTE and costs, The normal BYCOL function only allows the operation to return a scalar value (like a single sum) which it will collect int a row array. Here each output is to be a 4x4 array which are then to be stacked across the sheet. To do this, I wrote a variant of BYCOL which actually uses REDUCE/HSTACK to perform the calculation.
Code:
BYCOLλ(array, fnλ)
= LAMBDA(
    REDUCE(
      fnλ(TAKE(array,, 1)),
      SEQUENCE(COLUMNS(array) - 1, 1, 2),
      LAMBDA(acc, k,
        LET(
          currentCol,   CHOOSECOLS(array, k),
          processedCol, fnλ(currentCol),
          HSTACK(acc,   processedCol)
        )
      )
    )
  )
The BYCOLλ function itself is reusable. Something that may be a little unexpected is the way the function ApplyFactorλ is passed as a parameter within the worksheet formula.
Code:
ApplyFactorλ(array)
= LAMBDA(array,
    LAMBDA(pc, array * pc)
    )
  )
= BYCOLλ(Normaliseλ(percentage), ApplyFactorλ(overallData))
 

Attachments

  • Formula for Cost Split.xlsx
    24.2 KB · Views: 4
I hadn't realised your headers were conditionally formatted. No sooner had I entered the formula
Code:
= BYCOLλ(sector,
    LAMBDA(s, s & " Country " & headings)
  )
than all the headers were there in neatly formatted
83162
 
Back
Top