Hello all. I have a list I am working with in excel 2003. The columns I am
interested in are Employee, Overtime Code, Pay and Comp. There are
approximately 50
overtime codes. I need to total the pay and comp columns for each employee.
My problem is that if the OT codes are certain numbers, they will not be used
in the calcualtions. Like so:
EMPLOYEE OT CODE PAY COMP
Jones 1 4 0
Jones 6 4 0
Jones 5.02 0 4
Jones 18 2 0
Jones 9 0 4

Smith 5.02 0 4
Smith 7 1 0
Smith 9 2 0
Smith 17.4 0 1
Smith 1 0 4

The overtime codes of 1 and 9 are not to be used in the caluclations, so my
totals will be
Jones 6 4
Smith 1 5

Any help on how to do this? I can do it in either a list or a regular range
of cells. I thought I macro might help, but I'm having trouble figuring out
how to do it.

Thanks for any help.

I'd add a new column or two.

Give them the headers of "Adjusted Pay" and "Adjusted Comp"

Then fill them with formulas like:
=if(or(b2={1,6}),0,c2)
or to hide the 0's
=if(or(b2={1,6}),"",c2)

And the same for the Adjusted Comp field.

Then use data|subtotals and use these adjusted fields for your subtotals.

ps. I'd remove all the empty rows in my data, too. It just screws up the
control breaks when you use data|subtotal.


RM270 wrote:

Hello all. I have a list I am working with in excel 2003. The columns I am
interested in are Employee, Overtime Code, Pay and Comp. There are
approximately 50
overtime codes. I need to total the pay and comp columns for each employee.
My problem is that if the OT codes are certain numbers, they will not be used
in the calcualtions. Like so:
EMPLOYEE OT CODE PAY COMP
Jones 1 4 0
Jones 6 4 0
Jones 5.02 0 4
Jones 18 2 0
Jones 9 0 4

Smith 5.02 0 4
Smith 7 1 0
Smith 9 2 0
Smith 17.4 0 1
Smith 1 0 4

The overtime codes of 1 and 9 are not to be used in the caluclations, so my
totals will be
Jones 6 4
Smith 1 5

Any help on how to do this? I can do it in either a list or a regular range
of cells. I thought I macro might help, but I'm having trouble figuring out
how to do it.

Thanks for any help.

--

Dave Peterson

Thank you Dave. That will work. I hoped it would be something easy, and this
is. I don't suppose you know if I can print just the subtotals from a sort,
do you?

Thanks
PS my real data does not have blank rows. I just put it in my example to
emphasize
the name change.

"Dave Peterson" wrote:

I'd add a new column or two.

Give them the headers of "Adjusted Pay" and "Adjusted Comp"

Then fill them with formulas like:
=if(or(b2={1,6}),0,c2)
or to hide the 0's
=if(or(b2={1,6}),"",c2)

And the same for the Adjusted Comp field.

Then use data|subtotals and use these adjusted fields for your subtotals.

ps. I'd remove all the empty rows in my data, too. It just screws up the
control breaks when you use data|subtotal.


RM270 wrote:

Hello all. I have a list I am working with in excel 2003. The columns I am
interested in are Employee, Overtime Code, Pay and Comp. There are
approximately 50
overtime codes. I need to total the pay and comp columns for each employee.
My problem is that if the OT codes are certain numbers, they will not be used
in the calcualtions. Like so:
EMPLOYEE OT CODE PAY COMP
Jones 1 4 0
Jones 6 4 0
Jones 5.02 0 4
Jones 18 2 0
Jones 9 0 4

Smith 5.02 0 4
Smith 7 1 0
Smith 9 2 0
Smith 17.4 0 1
Smith 1 0 4

The overtime codes of 1 and 9 are not to be used in the caluclations, so my
totals will be
Jones 6 4
Smith 1 5

Any help on how to do this? I can do it in either a list or a regular range
of cells. I thought I macro might help, but I'm having trouble figuring out
how to do it.

Thanks for any help.

--

Dave Peterson
.

If you apply data|subtotals, you can use the outlining symbols at the left to
show or hide as many details as you want.

When you're happy, you can print what you see.



RM270 wrote:

Thank you Dave. That will work. I hoped it would be something easy, and this
is. I don't suppose you know if I can print just the subtotals from a sort,
do you?

Thanks
PS my real data does not have blank rows. I just put it in my example to
emphasize
the name change.

"Dave Peterson" wrote:

I'd add a new column or two.

Give them the headers of "Adjusted Pay" and "Adjusted Comp"

Then fill them with formulas like:
=if(or(b2={1,6}),0,c2)
or to hide the 0's
=if(or(b2={1,6}),"",c2)

And the same for the Adjusted Comp field.

Then use data|subtotals and use these adjusted fields for your subtotals.

ps. I'd remove all the empty rows in my data, too. It just screws up the
control breaks when you use data|subtotal.


RM270 wrote:

Hello all. I have a list I am working with in excel 2003. The columns I am
interested in are Employee, Overtime Code, Pay and Comp. There are
approximately 50
overtime codes. I need to total the pay and comp columns for each employee.
My problem is that if the OT codes are certain numbers, they will not be used
in the calcualtions. Like so:
EMPLOYEE OT CODE PAY COMP
Jones 1 4 0
Jones 6 4 0
Jones 5.02 0 4
Jones 18 2 0
Jones 9 0 4

Smith 5.02 0 4
Smith 7 1 0
Smith 9 2 0
Smith 17.4 0 1
Smith 1 0 4

The overtime codes of 1 and 9 are not to be used in the caluclations, so my
totals will be
Jones 6 4
Smith 1 5

Any help on how to do this? I can do it in either a list or a regular range
of cells. I thought I macro might help, but I'm having trouble figuring out
how to do it.

Thanks for any help.

--

Dave Peterson
.


--

Dave Peterson

Thank you Dave. You have been so helpful. It does just what I want it to do
now!

"Dave Peterson" wrote:

If you apply data|subtotals, you can use the outlining symbols at the left to
show or hide as many details as you want.

When you're happy, you can print what you see.



RM270 wrote:

Thank you Dave. That will work. I hoped it would be something easy, and this
is. I don't suppose you know if I can print just the subtotals from a sort,
do you?

Thanks
PS my real data does not have blank rows. I just put it in my example to
emphasize
the name change.

"Dave Peterson" wrote:

I'd add a new column or two.

Give them the headers of "Adjusted Pay" and "Adjusted Comp"

Then fill them with formulas like:
=if(or(b2={1,6}),0,c2)
or to hide the 0's
=if(or(b2={1,6}),"",c2)

And the same for the Adjusted Comp field.

Then use data|subtotals and use these adjusted fields for your subtotals.

ps. I'd remove all the empty rows in my data, too. It just screws up the
control breaks when you use data|subtotal.


RM270 wrote:

Hello all. I have a list I am working with in excel 2003. The columns I am
interested in are Employee, Overtime Code, Pay and Comp. There are
approximately 50
overtime codes. I need to total the pay and comp columns for each employee.
My problem is that if the OT codes are certain numbers, they will not be used
in the calcualtions. Like so:
EMPLOYEE OT CODE PAY COMP
Jones 1 4 0
Jones 6 4 0
Jones 5.02 0 4
Jones 18 2 0
Jones 9 0 4

Smith 5.02 0 4
Smith 7 1 0
Smith 9 2 0
Smith 17.4 0 1
Smith 1 0 4

The overtime codes of 1 and 9 are not to be used in the caluclations, so my
totals will be
Jones 6 4
Smith 1 5

Any help on how to do this? I can do it in either a list or a regular range
of cells. I thought I macro might help, but I'm having trouble figuring out
how to do it.

Thanks for any help.

--

Dave Peterson
.


--

Dave Peterson
.