Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
separating data in columns
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
separating data in columns
How about formulas like this
=if(and(A1=0,A1<=80),A1,"") =if(and(A1=81,A1<=120),A1,"") =if(a1120,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. "Tee" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
separating data in columns
I will try all until I find the one that works. Thank you so much!
"Barb Reinhardt" wrote: How about formulas like this =if(and(A1=0,A1<=80),A1,"") =if(and(A1=81,A1<=120),A1,"") =if(a1120,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. "Tee" wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting 2 colums of numbers and incremening them down | Excel Discussion (Misc queries) | |||
Merging two columns and keeping the data from both | Excel Worksheet Functions | |||
Sorting Data From One Column into Multiple Columns | Excel Worksheet Functions | |||
MATCH UP DATA IN COLUMNS | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) |