I have overstock in some DC"s, zero inventory in others, all with a rate of sale. I am trying to use the following formula to tell me where I reduce inventory in overstocked DC's and where I move inventory to understocked DC's but the total on hand inventory does not add up to the total optimal level on the other end of the output: I will post a picture of the formula but I will explain again if I didn't do it well the first time. I want to see, in a column, the amount of inventory I need to move (or keep) in each DC so I level off the total amount of inventory in all DC';s for the item.
I tried this formula =IF(B2 >= AVERAGE($B$2:$B$11), B2 - (B2 - AVERAGE($B$2:$B$11)) * (C2 / AVERAGE($C$2:$C$11)), B2 + (AVERAGE($B$2:$B$11) - B2) * (C2 / AVERAGE($C$2:$C$11))) but the output of the answer didn't total up to the total inventory I start with
