WPS calculates the number of banknotes in different denominations by wage

At present, some enterprises, factories using cash to pay the way, and for the financial staff to estimate the number of bills in various denominations has become an essential procedure for most people who do not understand VBA and functions is also a difficult point.

In fact, the ET2009 array formula can easily solve this problem, it takes only a few seconds to borrow an array formula to complete the amount of money required by all personnel. Now concretely demonstrate the requirements and completion steps.

Suppose that the denomination to be calculated includes 100 yuan, 50 yuan, 20 yuan, 10 yuan, 5 yuan, 2 yuan, 1 yuan (if you need to use the same formula in the corner and points, there is no difference in mentality), then in the Wage deposit column (assuming b column) to the right to establish 7 columns as a secondary area, used to store the number of banknotes per denomination.

1. Enter 100, 50, 20, 10, 5, 2, 1 respectively in the C1:i1 area;

2. Select the C1:i1 area, right-click, and select the Format Cells menu;

3. In the Number tab, in category, select Custom;

4. The "Type" box on the right shows "g/common format" and modifies it to "g/

Figure one custom number format

5. Enter the following formula in cell C2: =if (COLUMN () =3,int ($B 2/c$1), INT ($B 2-sum (OFFSET ($B $1,rows (A$1:A1), 1,1,columns ($B: B)-1) *offset ($ B$1,,1,1,columns ($B: B)-1))/c$1)

Input formula needs to be pressed at the same time "Ctrl+shift+enter" three key to end, that according to the array formula calculation, otherwise can not produce the correct results.

6. Select cell C1, fill the formula to the right to I2, and then double-click the fill handle to fill the C2:I2 formula down to the end. The calculated results of the formula are shown in Figure II:

Figure II calculates the number of banknotes by using an array formula

7. In order to verify the accuracy of the calculation, a secondary zone is established in the J column to summarize the product of all denominations and quantities. In J1 enter totals, enter the following formula in J2: =sum ($C $: $I $1*c2:i2)

It still ends with the "Ctrl+shift+enter" triple key, otherwise the correct result cannot be produced.

8. Double-click the fill handle of the J2 cell to fill the formula down to the last cell.

9. The correctness of the formula can be clearly distinguished by comparing the gross value of the J column with the wages in column B. See figure III for the following:

Figure III Verify the accuracy of the formula

Explanation of Formula Idea:

The simplest to calculate the number of banknotes in the 100-dollar denomination is to divide the wages by 100 and then use the INT function to get the whole, that is, the "int ($B 2/c$1)" section of the formula;

When calculating the number of banknotes in other denominations, only some of the remaining salary needs to be calculated. And how to determine the value of the bill has been calculated is the focus. In this example, "offset ($B $1,,1,1,columns ($B: B)-1)" is used to obtain the nominal value of the banknotes that have been calculated, and then "offset" ($B $1,rows (A$1:A1), 1,1,columns ($B: B)-1) " Gets the number of the calculated banknotes, which are multiplied and aggregated, and the total payroll is the remaining amount to be calculated.

and "two-area product and sum" has a special function--mmult in ET, so this example formula can be changed to "=if (COLUMN () =3,int ($B 2/c$1), INT ($B 2-mmult (OFFSET ($B $1,rows (A$1:A1), 1,1,columns ($B: B)-1)

, Transpose (OFFSET ($B $1,,1,1,columns ($B: B)-1))) "/c$1)"

According to the previous analysis, different formulas were used to calculate the quantity of 100-dollar banknotes and the number of other denominations. In order for the two to be unified, that is, use only one formula to complete, through the IF function to combine the two-paragraph formula, so that the formula in the 3rd column is calculated in the previous way, the column number greater than 3 is calculated in another way.

Finally, if you work with similar software Excel to solve this problem, you can use the following common formula to complete:

=if (COLUMN () =3,int ($B 2/c$1), INT (($B 2-sumproduct (B2: $C 2*b$1: $C $))