Example 4: Reducing Values to Negative Numbers - Value Change Distributed

Suppose that:

  • The value of the MASS_EDIT_BY_VALUE_REDUCTION_MODE registry parameter is ALWAYS_APPLY. This means that any value that would be reduced to a negative number because of a mass-edit by value is reduced to a negative number, according to the specified mass-edit by value option and the distribution rules.
  • The Value Change Distributed option is selected on the Mass-Edit Criteria pop-up window.

Suppose that you want to apply a decrease of $300,000 to an editable column on the Worksheet to the CORPORATE location, and you want to distribute this change to all jewelry departments. Based on the filter criteria, there are 5 departments, with the following values in each department:

Location Initial Value
Jewelry department 1 $50,000
Jewelry department 2 $75,000
Jewelry department 3 $30,000
Jewelry department 4 $45,000
Jewelry department 5 $50,000
Total of these jewelry departments $250,000

The value to distribute ($300,000) is greater than the initial total value for all departments ($250,000). Therefore, the new total for these departments is -$50,000.

The new value for each department is calculated using the following formula:

(total value after mass-edit/total initial value) x initial department value

Note that this is the same formula used in "Example 2: Value Change Distributed" on page 271 .

  • Jewelry department 1

    = (-$50,000/$250,000) x$50,000

    = -$10,000

  • Jewelry department 2

    = (-$50,000/$250,000) x $75,000

    = -$15,000

  • Jewelry department 3

    = (-$50,000/$250,000) x $30,000

    = -$6,000

  • Jewelry department 4

    = ( -$50,000/$250,000) x $45,000

    = -$9,000

  • Jewelry department 5

    = (-$50,000/$250,000) x $50,000

    = -$10,000

After the mass-edit, the departments have the following values:

Location Initial Value Mass-Edited Hours (By Value Change Distributed)
Jewelry department 1 $50,000 -$10,000
Jewelry department 2 $75,000 -$15,000
Jewelry department 3 $30,000 -$6,000
Jewelry department 4 $45,000 -$9,000
Jewelry department 5 $50,000 -$10,000
Total of these jewelry departments $250,000 -$50,000