Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a solution
I'm fairly comfortable around Excel with formulas and pivot tables but have
encountered an opportunity I have not yet faced. I have a spreadsheet with 3 tabs. The first tab contains HR information and has one row per employee. The second tab contains Util Hrs and can have up to three rows per employee. The Third tab contains Hrs Worked and can contain up to thirteen rows per employee. I need to summarize the information in the 2nd and 3rd tabs to return a single row of additional information back to the 1st tab (which I can do using the LOOKUP function). What I don't know how to do is apply a formula to the 2nd and 3rd tabs to give me a unique value per employee. Below is an example of the data: Tab 1 Tab 2 Tab 3 Col A Col A Col B Col C Col A Col B Col C ANDPE01 ANDPE01 176 22.5 ANDPE01 40 39.5 ANDPE01 160 30 ANDPE01 40 40 ANDPE01 160 64.5 ANDPE01 40 32 ANDPE01 40 37.5 What I expect to be returned to Tab 1 is: For ANDPE01 Col A= SUM(Tab2:Col C) / SUM(Tab2:Col B) which would be .2358 Col B=SUM(Tab3:Col C) / SUM(Tab3:Col B) which would be ..9312 Any suggestions? I have approximately 2500 rows in the first tab that need to have this consolidation performed. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking for a solution
I'm afraid your data got a little skewed when you posted. However, based on
the last bit where you're stating what you want, it sounds like you need to do a couple of sums based on a name. In which case, maybe something like: =SUMIF('Tab 2'!A:A,A2,C:C)/SUMIF('Tab 2'!A:A,A2,C:C) and =SUMIF('Tab 3'!A:A,A2,C:C)/SUMIF('Tab 3'!A:A,A2,C:C) If this isn't what your looking for, perhaps retry posting your data (organize downward, avoid going across, if possible). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "CA Linda" wrote: I'm fairly comfortable around Excel with formulas and pivot tables but have encountered an opportunity I have not yet faced. I have a spreadsheet with 3 tabs. The first tab contains HR information and has one row per employee. The second tab contains Util Hrs and can have up to three rows per employee. The Third tab contains Hrs Worked and can contain up to thirteen rows per employee. I need to summarize the information in the 2nd and 3rd tabs to return a single row of additional information back to the 1st tab (which I can do using the LOOKUP function). What I don't know how to do is apply a formula to the 2nd and 3rd tabs to give me a unique value per employee. Below is an example of the data: Tab 1 Tab 2 Tab 3 Col A Col A Col B Col C Col A Col B Col C ANDPE01 ANDPE01 176 22.5 ANDPE01 40 39.5 ANDPE01 160 30 ANDPE01 40 40 ANDPE01 160 64.5 ANDPE01 40 32 ANDPE01 40 37.5 What I expect to be returned to Tab 1 is: For ANDPE01 Col A= SUM(Tab2:Col C) / SUM(Tab2:Col B) which would be .2358 Col B=SUM(Tab3:Col C) / SUM(Tab3:Col B) which would be .9312 Any suggestions? I have approximately 2500 rows in the first tab that need to have this consolidation performed. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Looking for a better solution | Excel Worksheet Functions | |||
solution wants | Excel Discussion (Misc queries) | |||
looking for a solution | Excel Worksheet Functions | |||
Need a pop up solution! | Excel Discussion (Misc queries) | |||
My solution | Excel Discussion (Misc queries) |