In a table, two of the columns contain the following:

Col A Col. B Col. C
Row 1 Employee Name 1% 7%
Row 2 Williams 5 Blank
Row 3 Peter Blank 42
Row 4 David Blank Blank

and so on.

For each Row, both Col. B and C can be blank but both of them can not be non
blank. In Columns D & E, I want a look up formula which returns the
following results:

Col A Col. B Col. C
Col. D Col. E
Row 1 Employee Name 1% 7%
Row 2 Williams 5 Blank
1% 5
Row 3 Peter Blank 42
7% 42
Row 4 David Blank Blank
Blank Blank


I need this for a Paystub Report in my excel 2007 payroll workbook. Under a
particular head some employees are charged 1% of their basic pay, some pay
7% and other pay nothing.

Any help will be highly appreciated.

TIA.

Rafeek.

On Mon, 26 Sep 2011 10:49:46 +0300, "Rafeek" wrote:

I need this for a Paystub Report in my excel 2007 payroll workbook. Under a
particular head some employees are charged 1% of their basic pay, some pay
7% and other pay nothing.

Any help will be highly appreciated.

TIA.

Rafeek.

Try reposting using a proportional font.

In message of Mon, 26 Sep
2011 02:32:28 in microsoft.public.excel.worksheet.functions,
FatBytestard writes
On Mon, 26 Sep 2011 10:49:46 +0300, "Rafeek" wrote:

I need this for a Paystub Report in my excel 2007 payroll workbook. Under a
particular head some employees are charged 1% of their basic pay, some pay
7% and other pay nothing.

Any help will be highly appreciated.

TIA.

Rafeek.

Try reposting using a proportional font.

That may be the issue. It may also be the OP has copied data from Excel
and pasted it into Microsoft Outlook Express 6.00.2900.3138.
Doing that causes column values to be padded with tab characters which
may be converted to sequences of one or more spaces.
I agree that a proportional font is inappropriate for table
illustrations.

I think the OP posted

A B C
1 Employee Name 1% 7%
2 Williams 5
3 Peter 42
4 David

and

A B C D E
1 Employee Name 1% 7%
2 Williams 5 1% 5
3 Peter 42 7% 42
4 David

If that is so,
1) Make the format of Column D, percentage with 0 decimal places (or
make all data text).
2) Set D2 to =IF(B2<"",$B$1,IF(C2<"",$C$1,"")) and copy down.
3) set E2 to =IF(D3=$B$1,B3,IF(C3<"",C3,"")) and copy down.

N.B. I don't check (Bn<"") < (Cn<""). i.e. I don't check that both 1%
and 7% are not checked.


I think I would prefer to see the data in two tables. e.g.

A B C D
1 Emp Name Pay Rate Sum
2 Williams 5 1 5
3 Peter 42 2 42
4 David 0

A B
1 Code Rate
2 0
3 1 1%
4 2 7%

I confess I do not have a full understanding of the OP's data. ;)
--
Walter Briscoe

Sorry, I messed up. What I intended to post was this: (hope it will come all
risght this time)

In a table, three columns contain the following:

Col A Col. B Col. C
Row 1 Employee Name 1% 7%
Row 2 Williams 5 Blank
Row 3 Peter Blank 42
Row 4 David Blank Blank
.................
.................

In each Row, both Col. B and C can be blank but both of them can not be non
blank. In Columns D & E, I want a look up formula which returns the
following results:

Col. A Col. B Col. C
Col. D Col. E
Row 1 Employee Name 1% 7%
Row 2 Williams 5 Blank
1% 5
Row 3 Peter Blank 42
7% 42
Row 4 David Blank Blank
Blank Blank
................
................

I need this for a Paystub Report in my excel 2007 payroll workbook. Under a
particular head some employees are charged 1% of their basic pay, some pay
7% and others pay nothing. If there are any deductions, it will be either 1%
or 7%. So in 2 adjoing cells in the Pay Slip, I want to show the % and the
amount deducted. If nothing was deducted, I want to leave these two cells
blank.

Any help will be highly appreciated.

TIA.

Rafeek.

"Walter Briscoe" wrote in message
...
In message of Mon, 26 Sep
2011 02:32:28 in microsoft.public.excel.worksheet.functions,
FatBytestard writes
On Mon, 26 Sep 2011 10:49:46 +0300, "Rafeek" wrote:

I need this for a Paystub Report in my excel 2007 payroll workbook. Under
a
particular head some employees are charged 1% of their basic pay, some
pay
7% and other pay nothing.

Any help will be highly appreciated.

TIA.

Rafeek.

Try reposting using a proportional font.

That may be the issue. It may also be the OP has copied data from Excel
and pasted it into Microsoft Outlook Express 6.00.2900.3138.
Doing that causes column values to be padded with tab characters which
may be converted to sequences of one or more spaces.
I agree that a proportional font is inappropriate for table
illustrations.

I think the OP posted

A B C
1 Employee Name 1% 7%
2 Williams 5
3 Peter 42
4 David

and

A B C D E
1 Employee Name 1% 7%
2 Williams 5 1% 5
3 Peter 42 7% 42
4 David

If that is so,
1) Make the format of Column D, percentage with 0 decimal places (or
make all data text).
2) Set D2 to =IF(B2<"",$B$1,IF(C2<"",$C$1,"")) and copy down.
3) set E2 to =IF(D3=$B$1,B3,IF(C3<"",C3,"")) and copy down.

N.B. I don't check (Bn<"") < (Cn<""). i.e. I don't check that both 1%
and 7% are not checked.


I think I would prefer to see the data in two tables. e.g.

A B C D
1 Emp Name Pay Rate Sum
2 Williams 5 1 5
3 Peter 42 2 42
4 David 0

A B
1 Code Rate
2 0
3 1 1%
4 2 7%

I confess I do not have a full understanding of the OP's data. ;)
--
Walter Briscoe

On Sep 26, 9:09*am, "Rafeek" wrote:
Sorry, I messed up. What I intended to post was this: (hope it will come all
risght this time)

In a table, three columns contain the following:

* * * * * * * * Col A * * * * * * * * * * * *Col. B * * * *Col. C
Row 1 * * *Employee Name * * * * * * 1% * * * * * *7%
Row 2 * * *Williams * * * * * * * * * * * * 5 * * * * * * *Blank
Row 3 * * *Peter * * * * * * * * * * * * * Blank * * * * * 42
Row 4 * * *David * * * * * * * * * * * * *Blank * * * * *Blank
................
................

In each Row, both Col. B and C can be blank but both of them can not be non
blank. In Columns D & E, I want a look up formula which returns the
following results:

* * * * * * * *Col. A * * * * * * * * * *Col. B * * * * * *Col. C
Col. D * *Col. E
Row 1 * *Employee Name * * * * * *1% * * * * * * * *7%
Row 2 * *Williams * * * * * * * * * * * *5 * * * * * * * * * *Blank
1% * * * *5
Row 3 * *Peter * * * * * * * * * * * * * *Blank * * * * * *42
7% * * * *42
Row 4 * *David * * * * * * * * * * * * * Blank * * * * * * * Blank
Blank * *Blank
...............
...............

I need this for a Paystub Report in my excel 2007 payroll workbook. Under a
particular head some employees are charged 1% of their basic pay, some pay
7% and others pay nothing. If there are any deductions, it will be either 1%
or 7%. So in 2 adjoing cells in the Pay Slip, I want to show the % and the
amount deducted. If nothing was deducted, I want to leave these two cells
blank.

Any help will be highly appreciated.

TIA.

Rafeek.

"Walter Briscoe" wrote in message

...



In message of Mon, 26 Sep
2011 02:32:28 in microsoft.public.excel.worksheet.functions,
FatBytestard writes
On Mon, 26 Sep 2011 10:49:46 +0300, "Rafeek" wrote:

I need this for a Paystub Report in my excel 2007 payroll workbook. Under
a
particular head some employees are charged 1% of their basic pay, some
pay
7% and other pay nothing.

Any help will be highly appreciated.

TIA.

Rafeek.

*Try reposting using a proportional font.

That may be the issue. It may also be the OP has copied data from Excel
and pasted it into Microsoft Outlook Express 6.00.2900.3138.
Doing that causes column values to be padded with tab characters which
may be converted to sequences of one or more spaces.
I agree that a proportional font is inappropriate for table
illustrations.

I think the OP posted

* * * *A * * * * * * * B * * * C
1 * * * Employee Name * 1% * * *7%
2 * * * Williams * * * * 5
3 * * * Peter * * * * * * * * * 42
4 * * * David

and

* * * *A * * * * * * * B * * * C * * * D * * * E
1 * * * Employee Name * 1% * * *7%
2 * * * Williams * * * * 5 * * * * * * 1% * * * 5
3 * * * Peter * * * * * * * * * 42 * * 7% * * *42
4 * * * David

If that is so,
1) Make the format of Column D, percentage with 0 decimal places (or
make all data text).
2) Set D2 to =IF(B2<"",$B$1,IF(C2<"",$C$1,"")) and copy down.
3) set E2 to =IF(D3=$B$1,B3,IF(C3<"",C3,"")) and copy down.

N.B. I don't check (Bn<"") < (Cn<""). i.e. I don't check that both 1%
and 7% are not checked.

I think I would prefer to see the data in two tables. e.g.

* * * *A * * * * * * * B * * * C * * * D
1 * * * Emp Name * * *Pay * *Rate * * Sum
2 * * * Williams * * * *5 * * * 1 * * * 5
3 * * * Peter * * * * *42 * * * 2 * * *42
4 * * * David * * * * * * * * * 0

* * * *A * * * * * * * B
1 * * * Code * * * * Rate
2 * * * * *0
3 * * * * *1 * * * * * 1%
4 * * * * *2 * * * * * 7%

I confess I do not have a full understanding of the OP's data. ;)
--
Walter Briscoe- Hide quoted text -

- Show quoted text -

I'm not really sure I understand what you're trying to do.

If I understand correctly, each employee can work for a head & each
head has a specified tax rate, which is either 1%, 7%, or blank which
would probably be better handled as 0%, but you may be handling that
elsewhere with a formula.

It's technically possible to do what you've asked for with an OFFSET()
function that has a nested MATCH().

However, if each head has only one tax rate, it would seem to make
more sense to break the heads into a separate list, then lookup that
head's tax rate.

Or have I missed it entirely?