3D Array Payroll
 

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

3D Array Payroll
 

Use

=B2*SUM(Reference!$B2:$D2)

and copy down and across

--
__________________________________
HTH

Bob

"sgl" wrote in message
...
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

3D Array Payroll
 

Bob,

Thank you very much that works very well! I solved the problem using

{=SUMPRODUCT(--($A24=WageStructure!$E9)*(WageStructure!$E$10:$E$2 9)*('TaxesMatrix (2)'!C$2:C$21))}

However, your way much simpler as with my solution I had to change the
column reference each time I copied the formula down.

Thanks again/sgl

"Bob Phillips" wrote:

Use

=B2*SUM(Reference!$B2:$D2)

and copy down and across

--
__________________________________
HTH

Bob

"sgl" wrote in message
...
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