separating data in columns

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tee - 26 Oct 2006 15:56 GMT

Hello. I have a worksheet with several columns of information. What I need
help with are only two of the columns. One column lists the number of
vacation hours available for each employee of our company. The other column
is their rate of pay. Both columns have been formulated to multiply one by
the other and subtotaled.

Now, I need to create 3 more separate columns that will give a break down of
the various vacation hours such as.....0-80 hours in one column, 81-120 hours
in another and 121 hours and above in another column and grand total each
column. Hope this makes sense. I hope someone can help. Thanks

Reply to this Message

Barb Reinhardt - 26 Oct 2006 20:09 GMT

How about formulas like this

=if(and(A1>=0,A1<=80),A1,"")

=if(and(A1>=81,A1<=120),A1,"")

=if(a1>120,A1,"")

For the grand total, since you have non numerics, it's a bit tricker.

Let's say one column of data is in B2:B100, for this example

=SUM(IF(B2:B100>=81,IF(B2:B100<=120,B2:B100)))

Commit with CTRL SHIFT ENTER. That should put {} around the formula.

> Hello. I have a worksheet with several columns of information. What I need
> help with are only two of the columns. One column lists the number of
[quoted text clipped - 6 lines]
> in another and 121 hours and above in another column and grand total each
> column. Hope this makes sense. I hope someone can help. Thanks

Reply to this Message

Tee - 27 Oct 2006 13:59 GMT

I will try all until I find the one that works. Thank you so much!

> How about formulas like this
>
[quoted text clipped - 22 lines]
> > in another and 121 hours and above in another column and grand total each
> > column. Hope this makes sense. I hope someone can help. Thanks

Reply to this Message