7 Replies Latest reply on Jun 28, 2018 1:15 PM by Shinichiro Murakami

# Dividing 1 sum amount into others

Having a heck of a time wrapping my head around what I want to call this.

I'm attempting to take one amount of my table and divide it among the rest pro rata.

Example below would be taking the Sum of Dept 1 to 3 and then figuring out that total total (1700) and % of total for each department from that calculation then giving that percentage of Dept 4 to it to produce my end Values.

The initial value column is a sum of months currently set to filter for the prior 12 months.

DepartmentValueEnd Value
Dept 1500470.59
Dept 2700658.82
Dept 3500470.59
Dept 4-1000
Total16001600

I'd prefer to do this on the fly vs adjusting my data but I'll do what I need to!

Thanks for the help!

• ###### 1. Re: Dividing 1 sum amount into others

HI Tyler

Could you please attach your sample data as twbx format.

Thanks,

Shin

• ###### 2. Re: Dividing 1 sum amount into others

Hi Tyler,

I am up to this.

Not sure how you are calculating your last column value.

~Tushar

• ###### 3. Re: Dividing 1 sum amount into others

Hi Tushar,

The calculation works as such:

I'm taking the first three departments and finding out their weighting:

500+700+500 = 1700 which gives a weightings of 500/1700 = .294118 700/1700 = .411765

Then I take Department 4 and allocate it into the other 3 as based on that pro rata weighting

Dept 1 = 500 + (-100*.294118) = 470.59

Dept 2 = 700 + (-100*.411765) = 658.82

Dept 3 = 500 + (-100*.294118) = 470.59

Basically, all of these departments are the sum of a number of months and then are being allocated a general overhead portion from an overseeing group from the same period of time.

Hopefully that clarifys

• ###### 4. Re: Dividing 1 sum amount into others

I'll see if I can sterilize some here today to do that Shin. Thank you.

• ###### 5. Re: Dividing 1 sum amount into others

HI Tyler,

Hope this helps.

Thanks,

Shin

1 of 1 people found this helpful
• ###### 6. Re: Dividing 1 sum amount into others

This is a great step. And I think applying it to my workbook with a couple tweaks might do it perfect for me here.

Thanks!

• ###### 7. Re: Dividing 1 sum amount into others

HI Tyler

You are very welcome.

Shin