Allocation Excel Formula Feedback Appreciated

[PNau]

I held off on asking how to do this until I researched and came up with the attached solution, but would appreciate feedback if this could be done with more sophisticated tools or formulas to enhance my learning.  Plus sharing my solution for others.

I want to allocate unassigned sales to other locations, by multiplying the unassigned sales against the quotient of the locations sales over the total assigned sales.  I could not figure out a way to accomplish multiplying by a result in a row (e.g., if statement field / row combination based on unassigned's sales). Instead I focused on creating new fields (columns).

Thank you.

[bharti_dalal · Accepted solution]

hi @PNau,

I am attaching my solution. i used cross tab and transpose tool with multifield forlmula. This way you dont need to create more of columns. see if that helps.

[PNau]

Thank you, I like your cleaner WorkFlow better and I now see how to create new fields from the Current to the New_ in the Formula Tool. 

One item to note since Unassigned is sorted in my live data of fifty states it is not last in Record ID sort, but when the New_Unassigned field is created in the formula to make negative it is last on that side prior to joining by record order.  I am going to either just rename Unassigned ZUnassigned so it is last in sequence in both sides before the last join or see if adding a sort will work for each side. I could rename the new fields I know without the New_ but I may also have to deal with removing the _ added to South_Dakota on one side but not the other...if this does not throw the record sequence out of order, the Sort tool will be fine. Other option I know is to add a record ID field and use that, which I will try before using the simple ZUnassigned.

Thank you, just keeping track of my thought process when I refer to this response many times on how to resolve.