| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
comparing totals in 2 column
I have a very large spreadhseet. The spreadsheet looks similar to the one
below. I want to compare the totals (by employee) in column B with the totals (by employee) in column C. If the total in column B is larger, then I want to transfer those values to column D. If the total in column C is larger, then I want to transfer THOSE values to column D. As you can see, for ID 1 ..the total of column C is greater, so those values are carried over to column D. For ID 2 ..the total of column B is greater, so those values are carried over. Since there are a large number of employees, how do I set up a formula to compare these values by employee and make the transfer? Thanks! A B C D 1 ID Sked Act Greater 2 1 5 6 6 3 1 7 5 5 4 1 2 4 4 5 2 3 4 3 6 2 10 2 10 7 2 5 4 5 8 2 6 9 6 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
comparing totals in 2 column
I can do this .sort of ..with the following formula in cell D2, then copied
down to the subsequent rows for that ID: =IF(SUM($B$2:$B$4)SUM($C$2:$C$4),B2,C2) But ..this would require editing the formula at the top line of each ID, because the row references change, and each ID has a variable number of rows. There are over 400 ID's so I am hoping to find a formula I can type into just the top row,and copy to the bottom of the worksheet. "richzip" wrote: I have a very large spreadhseet. The spreadsheet looks similar to the one below. I want to compare the totals (by employee) in column B with the totals (by employee) in column C. If the total in column B is larger, then I want to transfer those values to column D. If the total in column C is larger, then I want to transfer THOSE values to column D. As you can see, for ID 1 ..the total of column C is greater, so those values are carried over to column D. For ID 2 ..the total of column B is greater, so those values are carried over. Since there are a large number of employees, how do I set up a formula to compare these values by employee and make the transfer? Thanks! A B C D 1 ID Sked Act Greater 2 1 5 6 6 3 1 7 5 5 4 1 2 4 4 5 2 3 4 3 6 2 10 2 10 7 2 5 4 5 8 2 6 9 6 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
comparing totals in 2 column
I'm following you on this, but, what should happen if the totals are equal?
1...1...2 1...2...1 1...1...1 Both totals are 4 -- Biff Microsoft Excel MVP "richzip" wrote in message ... I have a very large spreadhseet. The spreadsheet looks similar to the one below. I want to compare the totals (by employee) in column B with the totals (by employee) in column C. If the total in column B is larger, then I want to transfer those values to column D. If the total in column C is larger, then I want to transfer THOSE values to column D. As you can see, for ID 1 ..the total of column C is greater, so those values are carried over to column D. For ID 2 ..the total of column B is greater, so those values are carried over. Since there are a large number of employees, how do I set up a formula to compare these values by employee and make the transfer? Thanks! A B C D 1 ID Sked Act Greater 2 1 5 6 6 3 1 7 5 5 4 1 2 4 4 5 2 3 4 3 6 2 10 2 10 7 2 5 4 5 8 2 6 9 6 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
comparing totals in 2 column
In the meantime, this will do what you want:
=IF(SUMIF(A:A,A2,B:B)SUMIF(A:A,A2,C:C),B2,C2) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I'm following you on this, but, what should happen if the totals are equal? 1...1...2 1...2...1 1...1...1 Both totals are 4 -- Biff Microsoft Excel MVP "richzip" wrote in message ... I have a very large spreadhseet. The spreadsheet looks similar to the one below. I want to compare the totals (by employee) in column B with the totals (by employee) in column C. If the total in column B is larger, then I want to transfer those values to column D. If the total in column C is larger, then I want to transfer THOSE values to column D. As you can see, for ID 1 ..the total of column C is greater, so those values are carried over to column D. For ID 2 ..the total of column B is greater, so those values are carried over. Since there are a large number of employees, how do I set up a formula to compare these values by employee and make the transfer? Thanks! A B C D 1 ID Sked Act Greater 2 1 5 6 6 3 1 7 5 5 4 1 2 4 4 5 2 3 4 3 6 2 10 2 10 7 2 5 4 5 8 2 6 9 6 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
comparing totals in 2 column
Thank you! That worked perfectly. If the totals are the same, I will use
the values from column C, and it looks like this formula does that. Thanks again! "T. Valko" wrote: In the meantime, this will do what you want: =IF(SUMIF(A:A,A2,B:B)SUMIF(A:A,A2,C:C),B2,C2) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I'm following you on this, but, what should happen if the totals are equal? 1...1...2 1...2...1 1...1...1 Both totals are 4 -- Biff Microsoft Excel MVP "richzip" wrote in message ... I have a very large spreadhseet. The spreadsheet looks similar to the one below. I want to compare the totals (by employee) in column B with the totals (by employee) in column C. If the total in column B is larger, then I want to transfer those values to column D. If the total in column C is larger, then I want to transfer THOSE values to column D. As you can see, for ID 1 ..the total of column C is greater, so those values are carried over to column D. For ID 2 ..the total of column B is greater, so those values are carried over. Since there are a large number of employees, how do I set up a formula to compare these values by employee and make the transfer? Thanks! A B C D 1 ID Sked Act Greater 2 1 5 6 6 3 1 7 5 5 4 1 2 4 4 5 2 3 4 3 6 2 10 2 10 7 2 5 4 5 8 2 6 9 6 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
comparing totals in 2 column
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "richzip" wrote in message ... Thank you! That worked perfectly. If the totals are the same, I will use the values from column C, and it looks like this formula does that. Thanks again! "T. Valko" wrote: In the meantime, this will do what you want: =IF(SUMIF(A:A,A2,B:B)SUMIF(A:A,A2,C:C),B2,C2) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I'm following you on this, but, what should happen if the totals are equal? 1...1...2 1...2...1 1...1...1 Both totals are 4 -- Biff Microsoft Excel MVP "richzip" wrote in message ... I have a very large spreadhseet. The spreadsheet looks similar to the one below. I want to compare the totals (by employee) in column B with the totals (by employee) in column C. If the total in column B is larger, then I want to transfer those values to column D. If the total in column C is larger, then I want to transfer THOSE values to column D. As you can see, for ID 1 ..the total of column C is greater, so those values are carried over to column D. For ID 2 ..the total of column B is greater, so those values are carried over. Since there are a large number of employees, how do I set up a formula to compare these values by employee and make the transfer? Thanks! A B C D 1 ID Sked Act Greater 2 1 5 6 6 3 1 7 5 5 4 1 2 4 4 5 2 3 4 3 6 2 10 2 10 7 2 5 4 5 8 2 6 9 6 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Comparing one column of names to another | Excel Worksheet Functions | |||
| sum column totals | New Users to Excel | |||
| column totals | New Users to Excel | |||
| Calculating totals in a column based on a lookup in another column | Excel Worksheet Functions | |||
| Comparing/matching totals in a column to totals in a row | Excel Worksheet Functions | |||
Linear Mode
