SOLVED

Allocating cost formula

New Contributor

Good day

The table below shows a project # and in certain cases  multiple locations per project. The cost for each project is charged to "Central" Location as shown in the example below. I would like to create a formula that evenly allocates the cost charged at the central location to the states within each project. For example in project C 150k will be evenly allocated to MA & KY. Can anyone help?

Project #LocationCost
ACentral  200,000.00
ANY0
BCentral  300,000.00
BLA0
CCentral  300,000.00
CMA0
CKY0
DCentral  150,000.00
ECentral    10,000.00
EWY0
EIL0
ECA0
1 Reply
best response confirmed by dw700d (New Contributor)
Solution

@dw700d 

If I understand your request correctly:

 

S0262.png

 

The formula in D2, confirmed with Ctrl+Shift+Enter, is

 

=IF(B2="Central","",INDEX(C:C,MATCH(1,(A:A=A2)*(B:B="Central"),0))/(COUNTIF(A:A,A2)-1))

 

and this has been filled down.