3D Array Problem

[sgl]

My apologies this was wrongly posted in the Excel Programming group

Hi all,

I am using Excel 2003 with Vista.
I have a reference WkSht with all the Tax, Social Security, Union Dues rates
laid out against each element of the Wage Structure package. The Table is
laid out as follows:

Tax Soc
Union etc etc
Security
Dues

Basic Wages 10% 5% 3%
Leave Pay 7.5% 3% 3%
Subsistence 5.0% 2.5% 3%

In the Payroll Calculations WkSht I have the Payroll calculations for each
employee and for each element of the Wage Structure

Employee Name A B C
D etc etc

Basic Wages 1,000 750 650 500
Leave Pay 500 375 300 250
Subsistence 160 160 160 160
etc
etc

What I need to do is to calculate the various deductions against each
element of the Pay Structure in a separate table in the same WkSht as the
Payroll Calculations. The table is laid out as follows:

Employee Name A B C D etc
etc
Tax
Social Security
Union Dues
etc
etc

The layout of the Tables cannot be altered as this is part of a much larger
project.

Any assistance much appreciated/Many thanks/sgl

 

[Shane Devenshire]

Hi, Suppose your Wage Structure is located on a sheet called WageStructure
and your employee pay are in the range starting in A1, with titles on the
first row and in the first column and the first number in B2.

Then in the deduction range, say B7 enter the following formula

=SUMPRODUCT(Sheet3!B2*WageStructure!$B2:$D2)

Copy it down three rows and over as many columns as needed.

 

[sgl]

Thank you for your response Shane.
Unless I am doing something wrong your suggestion does not seem to work.
As an example for Employee A I would need to get the following results based
on what I have posted

Tax deductions
Basic wages 1,000 x 10% = 100.00
Leave Pay 500 x 7.5% = 37.50
Subsistence 160 x 5% = 8.00
Total Tax deductions 145.50

Social Security
Basic Wages 1,000 x 5% = 50.00
Leave Pay 500 x 3% = 15.00
Subsistence 160 x 2.5% = 4.00
Total Soc Sec deductions 69.00

Union Dues
Basic Wages 1,000 x 3% = 30.00
Leave Pay 500 x 3% = 15.00
Subsistence 160 x 3% = 4.80
Total Union Dues 49.80

The total deductions for Employee A would be 264.30
My apologies for being so analitical only trying to get the correct message
across.

many thanks/sgl