Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with creating a Macro
I am putting together a productivity report and I need some help with
creating a macro. I feel a macro would be the most accurate and efficient option for manipulating this data. If you think different, please review my question, then let me know. Thank you! Below is the column data I'm working with: Column A: Team Name Column B: Order # Column C: Customer Name Column D: # of Units Column E: # of Hours Column F: Start Time Column G: End Time Column H: # of Minutes I want to figure out the total units produced in an hour timeframe for (at the change of) each Order # (Column B). In general, I would like to create a macro that allows calculations for one order # that has data on multiple lines. (Please look at the equation(s) explanation below.) Here's what I'm looking at, as far as numbers are concerned: A B C D E F G H 1 Team: Order #: Customer: Units: Hours: Start: End: Minutes: 2 Blue 1234 ABC Store 550 12 2:27pm 3:34pm 77 3 Red 5678 DEF Store 2100 12 8:18am 9:40am 82 4 Green 5678 DEF Store 2100 9 9:42am 10:02am 20 So, if we were to calculate one order, that has data only on one row, that equation would be =(D2/(H2*E2))*60. This equation assumes the time for each order is only on a singular row. That, however, is not the case for most of the production times. Most of the order data are on multiple rows. For example, row 2 might capture all of the order's data (as would be the case for the (D2/(H2*E2))*60) equation), but others might take two rows or more. This would then change the equation to something like this: =(D4/((H3*E3)+(H4*E4)))*60 [assuming one order took the next two rows]. Some things to know: Even if an order populates data for more than one row, Column D: # of Units, does not change. For example, for order #5678 (in rows 2 and 3), the total production was 2100 units and the order information populated two rows, D3 and D4 all read 2100. (This also explain why my example equation: =(D4/((H3*E3)+(H4*E4)))*60 only takes cell D4 instead of adding cells D3 and D4 before dividing by ((H3*E3)+(H4*E4)).) I know this might seem confusing, but I would appreciate any comments you can provide. Thanks for all of your help! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with creating a Macro
If you want to calculate the total units produced per hour for one particular
order, then your requirement can be met with formulae... Here is what you need to do; Assuming you have data in the sheet named Sheet1 then in Sheet2 A1 Enter a Order# in B1 enter this formula =Vlookup(A1,Sheet1!B:D,3,False) to get # of Units for order# in A1 In C1 enter this formula =B1/(SUMPRODUCT(((Sheet1!E2:E100*60)+(Sheet1!H2:H100)) ,--(Sheet1!B2:B100=A1))) [Change 100 to end of your data set on Sheet1] Note: Your formula for total minutes should be E2*60 + H2 and not (E2+H2)*60!! By entering the Order# in A1 you can get the result for different Order#s. You can also enter unique Orders#s in Col A of Sheet2 and copy the formula down. Do change references to absolute references by changing E2:E100 to E$2:E$100 and so on... "Liz J" wrote: I am putting together a productivity report and I need some help with creating a macro. I feel a macro would be the most accurate and efficient option for manipulating this data. If you think different, please review my question, then let me know. Thank you! Below is the column data I'm working with: Column A: Team Name Column B: Order # Column C: Customer Name Column D: # of Units Column E: # of Hours Column F: Start Time Column G: End Time Column H: # of Minutes I want to figure out the total units produced in an hour timeframe for (at the change of) each Order # (Column B). In general, I would like to create a macro that allows calculations for one order # that has data on multiple lines. (Please look at the equation(s) explanation below.) Here's what I'm looking at, as far as numbers are concerned: A B C D E F G H 1 Team: Order #: Customer: Units: Hours: Start: End: Minutes: 2 Blue 1234 ABC Store 550 12 2:27pm 3:34pm 77 3 Red 5678 DEF Store 2100 12 8:18am 9:40am 82 4 Green 5678 DEF Store 2100 9 9:42am 10:02am 20 So, if we were to calculate one order, that has data only on one row, that equation would be =(D2/(H2*E2))*60. This equation assumes the time for each order is only on a singular row. That, however, is not the case for most of the production times. Most of the order data are on multiple rows. For example, row 2 might capture all of the order's data (as would be the case for the (D2/(H2*E2))*60) equation), but others might take two rows or more. This would then change the equation to something like this: =(D4/((H3*E3)+(H4*E4)))*60 [assuming one order took the next two rows]. Some things to know: Even if an order populates data for more than one row, Column D: # of Units, does not change. For example, for order #5678 (in rows 2 and 3), the total production was 2100 units and the order information populated two rows, D3 and D4 all read 2100. (This also explain why my example equation: =(D4/((H3*E3)+(H4*E4)))*60 only takes cell D4 instead of adding cells D3 and D4 before dividing by ((H3*E3)+(H4*E4)).) I know this might seem confusing, but I would appreciate any comments you can provide. Thanks for all of your help! "Liz J" wrote: I am putting together a productivity report and I need some help with creating a macro. I feel a macro would be the most accurate and efficient option for manipulating this data. If you think different, please review my question, then let me know. Thank you! Below is the column data I'm working with: Column A: Team Name Column B: Order # Column C: Customer Name Column D: # of Units Column E: # of Hours Column F: Start Time Column G: End Time Column H: # of Minutes I want to figure out the total units produced in an hour timeframe for (at the change of) each Order # (Column B). In general, I would like to create a macro that allows calculations for one order # that has data on multiple lines. (Please look at the equation(s) explanation below.) Here's what I'm looking at, as far as numbers are concerned: A B C D E F G H 1 Team: Order #: Customer: Units: Hours: Start: End: Minutes: 2 Blue 1234 ABC Store 550 12 2:27pm 3:34pm 77 3 Red 5678 DEF Store 2100 12 8:18am 9:40am 82 4 Green 5678 DEF Store 2100 9 9:42am 10:02am 20 So, if we were to calculate one order, that has data only on one row, that equation would be =(D2/(H2*E2))*60. This equation assumes the time for each order is only on a singular row. That, however, is not the case for most of the production times. Most of the order data are on multiple rows. For example, row 2 might capture all of the order's data (as would be the case for the (D2/(H2*E2))*60) equation), but others might take two rows or more. This would then change the equation to something like this: =(D4/((H3*E3)+(H4*E4)))*60 [assuming one order took the next two rows]. Some things to know: Even if an order populates data for more than one row, Column D: # of Units, does not change. For example, for order #5678 (in rows 2 and 3), the total production was 2100 units and the order information populated two rows, D3 and D4 all read 2100. (This also explain why my example equation: =(D4/((H3*E3)+(H4*E4)))*60 only takes cell D4 instead of adding cells D3 and D4 before dividing by ((H3*E3)+(H4*E4)).) I know this might seem confusing, but I would appreciate any comments you can provide. Thanks for all of your help! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with creating a Macro
Hi,
Another approach is to use a pivot table, this data looks consistant with such an approach. A second alternative is to use the Data, Subtotal command. Since you did not show us what or where you want the summary numbers the above approaches might do the trick. If you are trying to compute units per hour then your formula is incorrect. Here is a formula approach, assume you enter a list of all the order numbers in J2:J100. ( you can use the Data, Filter, Advanced Filter, Unique Records command to extract a unique list). In cell K2 I enterd the following formula: =MAX(IF(B$2:B$100=J2,D$2:D$100,0))/(SUMIF(B$2:B$100,J2,E$2:E$100)+SUMIF(B$2:B$100,J2, E$2:E$100)/60) This formula is array entered - Press Shift+Ctrl+Enter instead of Enter. Copy the formula down as far as necessary. Notes: If I understand your data you have the hours and you want to add the minutes and then divide the units by the total hours. 12 hrs + 77 min is 12 hrs + 77/60 hrs = 13.283 hrs and this total should be divided into 550 units. -- Thanks, Shane Devenshire "Liz J" wrote: I am putting together a productivity report and I need some help with creating a macro. I feel a macro would be the most accurate and efficient option for manipulating this data. If you think different, please review my question, then let me know. Thank you! Below is the column data I'm working with: Column A: Team Name Column B: Order # Column C: Customer Name Column D: # of Units Column E: # of Hours Column F: Start Time Column G: End Time Column H: # of Minutes I want to figure out the total units produced in an hour timeframe for (at the change of) each Order # (Column B). In general, I would like to create a macro that allows calculations for one order # that has data on multiple lines. (Please look at the equation(s) explanation below.) Here's what I'm looking at, as far as numbers are concerned: A B C D E F G H 1 Team: Order #: Customer: Units: Hours: Start: End: Minutes: 2 Blue 1234 ABC Store 550 12 2:27pm 3:34pm 77 3 Red 5678 DEF Store 2100 12 8:18am 9:40am 82 4 Green 5678 DEF Store 2100 9 9:42am 10:02am 20 So, if we were to calculate one order, that has data only on one row, that equation would be =(D2/(H2*E2))*60. This equation assumes the time for each order is only on a singular row. That, however, is not the case for most of the production times. Most of the order data are on multiple rows. For example, row 2 might capture all of the order's data (as would be the case for the (D2/(H2*E2))*60) equation), but others might take two rows or more. This would then change the equation to something like this: =(D4/((H3*E3)+(H4*E4)))*60 [assuming one order took the next two rows]. Some things to know: Even if an order populates data for more than one row, Column D: # of Units, does not change. For example, for order #5678 (in rows 2 and 3), the total production was 2100 units and the order information populated two rows, D3 and D4 all read 2100. (This also explain why my example equation: =(D4/((H3*E3)+(H4*E4)))*60 only takes cell D4 instead of adding cells D3 and D4 before dividing by ((H3*E3)+(H4*E4)).) I know this might seem confusing, but I would appreciate any comments you can provide. Thanks for all of your help! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with creating a Macro
Sheloo,
Thank you. It was nice of you to take the time to answer my question quickly! You're right, a Vlookup and sumproduct is the best route. I am confused, however, about the sumproduct formula given. Could you explain it a bit? =B1/(SUMPRODUCT(((Sheet1!E2:E100*60)+(Sheet1!H2:H100)) ,--(Sheet1!B2:B100=A1))) I have to admit, I've not used sumproduct to this extent before -- but, it seems I need to have some type of vlookup or matching formula to do a sumproduct to calculate a specific order number. I think (maybe) the last part of the equation: (Sheet1!B2:B100=A1))) is supposed to do that matching, but I am not sure. Could you please explain further? What is the "--" supposed to represent? "Sheeloo" wrote: If you want to calculate the total units produced per hour for one particular order, then your requirement can be met with formulae... Here is what you need to do; Assuming you have data in the sheet named Sheet1 then in Sheet2 A1 Enter a Order# in B1 enter this formula =Vlookup(A1,Sheet1!B:D,3,False) to get # of Units for order# in A1 In C1 enter this formula =B1/(SUMPRODUCT(((Sheet1!E2:E100*60)+(Sheet1!H2:H100)) ,--(Sheet1!B2:B100=A1))) [Change 100 to end of your data set on Sheet1] Note: Your formula for total minutes should be E2*60 + H2 and not (E2+H2)*60!! By entering the Order# in A1 you can get the result for different Order#s. You can also enter unique Orders#s in Col A of Sheet2 and copy the formula down. Do change references to absolute references by changing E2:E100 to E$2:E$100 and so on... "Liz J" wrote: I am putting together a productivity report and I need some help with creating a macro. I feel a macro would be the most accurate and efficient option for manipulating this data. If you think different, please review my question, then let me know. Thank you! Below is the column data I'm working with: Column A: Team Name Column B: Order # Column C: Customer Name Column D: # of Units Column E: # of Hours Column F: Start Time Column G: End Time Column H: # of Minutes I want to figure out the total units produced in an hour timeframe for (at the change of) each Order # (Column B). In general, I would like to create a macro that allows calculations for one order # that has data on multiple lines. (Please look at the equation(s) explanation below.) Here's what I'm looking at, as far as numbers are concerned: A B C D E F G H 1 Team: Order #: Customer: Units: Hours: Start: End: Minutes: 2 Blue 1234 ABC Store 550 12 2:27pm 3:34pm 77 3 Red 5678 DEF Store 2100 12 8:18am 9:40am 82 4 Green 5678 DEF Store 2100 9 9:42am 10:02am 20 So, if we were to calculate one order, that has data only on one row, that equation would be =(D2/(H2*E2))*60. This equation assumes the time for each order is only on a singular row. That, however, is not the case for most of the production times. Most of the order data are on multiple rows. For example, row 2 might capture all of the order's data (as would be the case for the (D2/(H2*E2))*60) equation), but others might take two rows or more. This would then change the equation to something like this: =(D4/((H3*E3)+(H4*E4)))*60 [assuming one order took the next two rows]. Some things to know: Even if an order populates data for more than one row, Column D: # of Units, does not change. For example, for order #5678 (in rows 2 and 3), the total production was 2100 units and the order information populated two rows, D3 and D4 all read 2100. (This also explain why my example equation: =(D4/((H3*E3)+(H4*E4)))*60 only takes cell D4 instead of adding cells D3 and D4 before dividing by ((H3*E3)+(H4*E4)).) I know this might seem confusing, but I would appreciate any comments you can provide. Thanks for all of your help! "Liz J" wrote: I am putting together a productivity report and I need some help with creating a macro. I feel a macro would be the most accurate and efficient option for manipulating this data. If you think different, please review my question, then let me know. Thank you! Below is the column data I'm working with: Column A: Team Name Column B: Order # Column C: Customer Name Column D: # of Units Column E: # of Hours Column F: Start Time Column G: End Time Column H: # of Minutes I want to figure out the total units produced in an hour timeframe for (at the change of) each Order # (Column B). In general, I would like to create a macro that allows calculations for one order # that has data on multiple lines. (Please look at the equation(s) explanation below.) Here's what I'm looking at, as far as numbers are concerned: A B C D E F G H 1 Team: Order #: Customer: Units: Hours: Start: End: Minutes: 2 Blue 1234 ABC Store 550 12 2:27pm 3:34pm 77 3 Red 5678 DEF Store 2100 12 8:18am 9:40am 82 4 Green 5678 DEF Store 2100 9 9:42am 10:02am 20 So, if we were to calculate one order, that has data only on one row, that equation would be =(D2/(H2*E2))*60. This equation assumes the time for each order is only on a singular row. That, however, is not the case for most of the production times. Most of the order data are on multiple rows. For example, row 2 might capture all of the order's data (as would be the case for the (D2/(H2*E2))*60) equation), but others might take two rows or more. This would then change the equation to something like this: =(D4/((H3*E3)+(H4*E4)))*60 [assuming one order took the next two rows]. Some things to know: Even if an order populates data for more than one row, Column D: # of Units, does not change. For example, for order #5678 (in rows 2 and 3), the total production was 2100 units and the order information populated two rows, D3 and D4 all read 2100. (This also explain why my example equation: =(D4/((H3*E3)+(H4*E4)))*60 only takes cell D4 instead of adding cells D3 and D4 before dividing by ((H3*E3)+(H4*E4)).) I know this might seem confusing, but I would appreciate any comments you can provide. Thanks for all of your help! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with creating a Macro
Array1: (Sheet1!E2:E100*60)+(Sheet1!H2:H100) returns an array by multiplying
each value in Col E with 60 and adding it to the corresponding value in Col H Array2: --(Sheet1!B2:B100=A1) returns an array of 1 and 0 based on whether condition is met or not... If you omitt -- then you get an array of TRUE and FALSE... Sumproduct multiplies the corresponding values, in Array1 and Array2, and adds them up. The 0 prevents the values not meeting the criterial from being added to the result. You can click on the = sign and then select the part of formula to see what it returns... or you can go through Evaluate formula option Did this work for you? "Liz J" wrote: Sheloo, Thank you. It was nice of you to take the time to answer my question quickly! You're right, a Vlookup and sumproduct is the best route. I am confused, however, about the sumproduct formula given. Could you explain it a bit? =B1/(SUMPRODUCT(((Sheet1!E2:E100*60)+(Sheet1!H2:H100)) ,--(Sheet1!B2:B100=A1))) I have to admit, I've not used sumproduct to this extent before -- but, it seems I need to have some type of vlookup or matching formula to do a sumproduct to calculate a specific order number. I think (maybe) the last part of the equation: (Sheet1!B2:B100=A1))) is supposed to do that matching, but I am not sure. Could you please explain further? What is the "--" supposed to represent? "Sheeloo" wrote: If you want to calculate the total units produced per hour for one particular order, then your requirement can be met with formulae... Here is what you need to do; Assuming you have data in the sheet named Sheet1 then in Sheet2 A1 Enter a Order# in B1 enter this formula =Vlookup(A1,Sheet1!B:D,3,False) to get # of Units for order# in A1 In C1 enter this formula =B1/(SUMPRODUCT(((Sheet1!E2:E100*60)+(Sheet1!H2:H100)) ,--(Sheet1!B2:B100=A1))) [Change 100 to end of your data set on Sheet1] Note: Your formula for total minutes should be E2*60 + H2 and not (E2+H2)*60!! By entering the Order# in A1 you can get the result for different Order#s. You can also enter unique Orders#s in Col A of Sheet2 and copy the formula down. Do change references to absolute references by changing E2:E100 to E$2:E$100 and so on... "Liz J" wrote: I am putting together a productivity report and I need some help with creating a macro. I feel a macro would be the most accurate and efficient option for manipulating this data. If you think different, please review my question, then let me know. Thank you! Below is the column data I'm working with: Column A: Team Name Column B: Order # Column C: Customer Name Column D: # of Units Column E: # of Hours Column F: Start Time Column G: End Time Column H: # of Minutes I want to figure out the total units produced in an hour timeframe for (at the change of) each Order # (Column B). In general, I would like to create a macro that allows calculations for one order # that has data on multiple lines. (Please look at the equation(s) explanation below.) Here's what I'm looking at, as far as numbers are concerned: A B C D E F G H 1 Team: Order #: Customer: Units: Hours: Start: End: Minutes: 2 Blue 1234 ABC Store 550 12 2:27pm 3:34pm 77 3 Red 5678 DEF Store 2100 12 8:18am 9:40am 82 4 Green 5678 DEF Store 2100 9 9:42am 10:02am 20 So, if we were to calculate one order, that has data only on one row, that equation would be =(D2/(H2*E2))*60. This equation assumes the time for each order is only on a singular row. That, however, is not the case for most of the production times. Most of the order data are on multiple rows. For example, row 2 might capture all of the order's data (as would be the case for the (D2/(H2*E2))*60) equation), but others might take two rows or more. This would then change the equation to something like this: =(D4/((H3*E3)+(H4*E4)))*60 [assuming one order took the next two rows]. Some things to know: Even if an order populates data for more than one row, Column D: # of Units, does not change. For example, for order #5678 (in rows 2 and 3), the total production was 2100 units and the order information populated two rows, D3 and D4 all read 2100. (This also explain why my example equation: =(D4/((H3*E3)+(H4*E4)))*60 only takes cell D4 instead of adding cells D3 and D4 before dividing by ((H3*E3)+(H4*E4)).) I know this might seem confusing, but I would appreciate any comments you can provide. Thanks for all of your help! "Liz J" wrote: I am putting together a productivity report and I need some help with creating a macro. I feel a macro would be the most accurate and efficient option for manipulating this data. If you think different, please review my question, then let me know. Thank you! Below is the column data I'm working with: Column A: Team Name Column B: Order # Column C: Customer Name Column D: # of Units Column E: # of Hours Column F: Start Time Column G: End Time Column H: # of Minutes I want to figure out the total units produced in an hour timeframe for (at the change of) each Order # (Column B). In general, I would like to create a macro that allows calculations for one order # that has data on multiple lines. (Please look at the equation(s) explanation below.) Here's what I'm looking at, as far as numbers are concerned: A B C D E F G H 1 Team: Order #: Customer: Units: Hours: Start: End: Minutes: 2 Blue 1234 ABC Store 550 12 2:27pm 3:34pm 77 3 Red 5678 DEF Store 2100 12 8:18am 9:40am 82 4 Green 5678 DEF Store 2100 9 9:42am 10:02am 20 So, if we were to calculate one order, that has data only on one row, that equation would be =(D2/(H2*E2))*60. This equation assumes the time for each order is only on a singular row. That, however, is not the case for most of the production times. Most of the order data are on multiple rows. For example, row 2 might capture all of the order's data (as would be the case for the (D2/(H2*E2))*60) equation), but others might take two rows or more. This would then change the equation to something like this: =(D4/((H3*E3)+(H4*E4)))*60 [assuming one order took the next two rows]. Some things to know: Even if an order populates data for more than one row, Column D: # of Units, does not change. For example, for order #5678 (in rows 2 and 3), the total production was 2100 units and the order information populated two rows, D3 and D4 all read 2100. (This also explain why my example equation: =(D4/((H3*E3)+(H4*E4)))*60 only takes cell D4 instead of adding cells D3 and D4 before dividing by ((H3*E3)+(H4*E4)).) I know this might seem confusing, but I would appreciate any comments you can provide. Thanks for all of your help! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with creating a Macro
Shane,
Thanks for your help. I was looking back over my original message and I realized I wrote a column header incorrectly. I meant to have written "# of Hourly" (meaning the amount of people on the order) for column E. The goal of this production analysis is to find out the total units produced in an hour timeframe for (at the change of) each Order #. So, with the data that I gave on the original message, I used the following equation: =D2/(H2*E2))*60 which translates to =((Total Units)/(Minutes for production * Amt of Hourly workers))*60. I originally used pivot tables to group my data, but I really need to do a sumproduct calculation to figure out the Minutes multiplied by the amount of workers for this equation. Am I doing this equation incorrectly now with this information? Thanks, Shane! "ShaneDevenshire" wrote: Hi, Another approach is to use a pivot table, this data looks consistant with such an approach. A second alternative is to use the Data, Subtotal command. Since you did not show us what or where you want the summary numbers the above approaches might do the trick. If you are trying to compute units per hour then your formula is incorrect. Here is a formula approach, assume you enter a list of all the order numbers in J2:J100. ( you can use the Data, Filter, Advanced Filter, Unique Records command to extract a unique list). In cell K2 I enterd the following formula: =MAX(IF(B$2:B$100=J2,D$2:D$100,0))/(SUMIF(B$2:B$100,J2,E$2:E$100)+SUMIF(B$2:B$100,J2, E$2:E$100)/60) This formula is array entered - Press Shift+Ctrl+Enter instead of Enter. Copy the formula down as far as necessary. Notes: If I understand your data you have the hours and you want to add the minutes and then divide the units by the total hours. 12 hrs + 77 min is 12 hrs + 77/60 hrs = 13.283 hrs and this total should be divided into 550 units. -- Thanks, Shane Devenshire "Liz J" wrote: I am putting together a productivity report and I need some help with creating a macro. I feel a macro would be the most accurate and efficient option for manipulating this data. If you think different, please review my question, then let me know. Thank you! Below is the column data I'm working with: Column A: Team Name Column B: Order # Column C: Customer Name Column D: # of Units Column E: # of Hours Column F: Start Time Column G: End Time Column H: # of Minutes I want to figure out the total units produced in an hour timeframe for (at the change of) each Order # (Column B). In general, I would like to create a macro that allows calculations for one order # that has data on multiple lines. (Please look at the equation(s) explanation below.) Here's what I'm looking at, as far as numbers are concerned: A B C D E F G H 1 Team: Order #: Customer: Units: Hours: Start: End: Minutes: 2 Blue 1234 ABC Store 550 12 2:27pm 3:34pm 77 3 Red 5678 DEF Store 2100 12 8:18am 9:40am 82 4 Green 5678 DEF Store 2100 9 9:42am 10:02am 20 So, if we were to calculate one order, that has data only on one row, that equation would be =(D2/(H2*E2))*60. This equation assumes the time for each order is only on a singular row. That, however, is not the case for most of the production times. Most of the order data are on multiple rows. For example, row 2 might capture all of the order's data (as would be the case for the (D2/(H2*E2))*60) equation), but others might take two rows or more. This would then change the equation to something like this: =(D4/((H3*E3)+(H4*E4)))*60 [assuming one order took the next two rows]. Some things to know: Even if an order populates data for more than one row, Column D: # of Units, does not change. For example, for order #5678 (in rows 2 and 3), the total production was 2100 units and the order information populated two rows, D3 and D4 all read 2100. (This also explain why my example equation: =(D4/((H3*E3)+(H4*E4)))*60 only takes cell D4 instead of adding cells D3 and D4 before dividing by ((H3*E3)+(H4*E4)).) I know this might seem confusing, but I would appreciate any comments you can provide. Thanks for all of your help! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help with creating a Macro
Thanks for your help. I was looking back over my original message and I
realized I wrote a column header incorrectly. I meant to have written "# of Hourly" (meaning the amount of people on the order) for column E. The goal of this production analysis is to find out the total units produced in an hour timeframe for (at the change of) each Order #. So, with the data that I gave on the original message, I used the following equation: =D2/(H2*E2))*60 which translates to =((Total Units)/(Minutes for production * Amt of Hourly workers))*60. Would this formula make sense based off of the information we discussed? =B1/(SUMPRODUCT(((Sheet1!$E$2:$E$4000)*(Sheet1!$H$2:$H $4000)),--(Sheet1!$B$2:$B$4000=A1)))*60 "Sheeloo" wrote: Array1: (Sheet1!E2:E100*60)+(Sheet1!H2:H100) returns an array by multiplying each value in Col E with 60 and adding it to the corresponding value in Col H Array2: --(Sheet1!B2:B100=A1) returns an array of 1 and 0 based on whether condition is met or not... If you omitt -- then you get an array of TRUE and FALSE... Sumproduct multiplies the corresponding values, in Array1 and Array2, and adds them up. The 0 prevents the values not meeting the criterial from being added to the result. You can click on the = sign and then select the part of formula to see what it returns... or you can go through Evaluate formula option Did this work for you? "Liz J" wrote: Sheloo, Thank you. It was nice of you to take the time to answer my question quickly! You're right, a Vlookup and sumproduct is the best route. I am confused, however, about the sumproduct formula given. Could you explain it a bit? =B1/(SUMPRODUCT(((Sheet1!E2:E100*60)+(Sheet1!H2:H100)) ,--(Sheet1!B2:B100=A1))) I have to admit, I've not used sumproduct to this extent before -- but, it seems I need to have some type of vlookup or matching formula to do a sumproduct to calculate a specific order number. I think (maybe) the last part of the equation: (Sheet1!B2:B100=A1))) is supposed to do that matching, but I am not sure. Could you please explain further? What is the "--" supposed to represent? "Sheeloo" wrote: If you want to calculate the total units produced per hour for one particular order, then your requirement can be met with formulae... Here is what you need to do; Assuming you have data in the sheet named Sheet1 then in Sheet2 A1 Enter a Order# in B1 enter this formula =Vlookup(A1,Sheet1!B:D,3,False) to get # of Units for order# in A1 In C1 enter this formula =B1/(SUMPRODUCT(((Sheet1!E2:E100*60)+(Sheet1!H2:H100)) ,--(Sheet1!B2:B100=A1))) [Change 100 to end of your data set on Sheet1] Note: Your formula for total minutes should be E2*60 + H2 and not (E2+H2)*60!! By entering the Order# in A1 you can get the result for different Order#s. You can also enter unique Orders#s in Col A of Sheet2 and copy the formula down. Do change references to absolute references by changing E2:E100 to E$2:E$100 and so on... "Liz J" wrote: I am putting together a productivity report and I need some help with creating a macro. I feel a macro would be the most accurate and efficient option for manipulating this data. If you think different, please review my question, then let me know. Thank you! Below is the column data I'm working with: Column A: Team Name Column B: Order # Column C: Customer Name Column D: # of Units Column E: # of Hours Column F: Start Time Column G: End Time Column H: # of Minutes I want to figure out the total units produced in an hour timeframe for (at the change of) each Order # (Column B). In general, I would like to create a macro that allows calculations for one order # that has data on multiple lines. (Please look at the equation(s) explanation below.) Here's what I'm looking at, as far as numbers are concerned: A B C D E F G H 1 Team: Order #: Customer: Units: Hours: Start: End: Minutes: 2 Blue 1234 ABC Store 550 12 2:27pm 3:34pm 77 3 Red 5678 DEF Store 2100 12 8:18am 9:40am 82 4 Green 5678 DEF Store 2100 9 9:42am 10:02am 20 So, if we were to calculate one order, that has data only on one row, that equation would be =(D2/(H2*E2))*60. This equation assumes the time for each order is only on a singular row. That, however, is not the case for most of the production times. Most of the order data are on multiple rows. For example, row 2 might capture all of the order's data (as would be the case for the (D2/(H2*E2))*60) equation), but others might take two rows or more. This would then change the equation to something like this: =(D4/((H3*E3)+(H4*E4)))*60 [assuming one order took the next two rows]. Some things to know: Even if an order populates data for more than one row, Column D: # of Units, does not change. For example, for order #5678 (in rows 2 and 3), the total production was 2100 units and the order information populated two rows, D3 and D4 all read 2100. (This also explain why my example equation: =(D4/((H3*E3)+(H4*E4)))*60 only takes cell D4 instead of adding cells D3 and D4 before dividing by ((H3*E3)+(H4*E4)).) I know this might seem confusing, but I would appreciate any comments you can provide. Thanks for all of your help! "Liz J" wrote: I am putting together a productivity report and I need some help with creating a macro. I feel a macro would be the most accurate and efficient option for manipulating this data. If you think different, please review my question, then let me know. Thank you! Below is the column data I'm working with: Column A: Team Name Column B: Order # Column C: Customer Name Column D: # of Units Column E: # of Hours Column F: Start Time Column G: End Time Column H: # of Minutes I want to figure out the total units produced in an hour timeframe for (at the change of) each Order # (Column B). In general, I would like to create a macro that allows calculations for one order # that has data on multiple lines. (Please look at the equation(s) explanation below.) Here's what I'm looking at, as far as numbers are concerned: A B C D E F G H 1 Team: Order #: Customer: Units: Hours: Start: End: Minutes: 2 Blue 1234 ABC Store 550 12 2:27pm 3:34pm 77 3 Red 5678 DEF Store 2100 12 8:18am 9:40am 82 4 Green 5678 DEF Store 2100 9 9:42am 10:02am 20 So, if we were to calculate one order, that has data only on one row, that equation would be =(D2/(H2*E2))*60. This equation assumes the time for each order is only on a singular row. That, however, is not the case for most of the production times. Most of the order data are on multiple rows. For example, row 2 might capture all of the order's data (as would be the case for the (D2/(H2*E2))*60) equation), but others might take two rows or more. This would then change the equation to something like this: =(D4/((H3*E3)+(H4*E4)))*60 [assuming one order took the next two rows]. Some things to know: Even if an order populates data for more than one row, Column D: # of Units, does not change. For example, for order #5678 (in rows 2 and 3), the total production was 2100 units and the order information populated two rows, D3 and D4 all read 2100. (This also explain why my example equation: =(D4/((H3*E3)+(H4*E4)))*60 only takes cell D4 instead of adding cells D3 and D4 before dividing by ((H3*E3)+(H4*E4)).) I know this might seem confusing, but I would appreciate any comments you can provide. Thanks for all of your help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a Macro? | Excel Discussion (Misc queries) | |||
VBA macro creating pdf | Excel Discussion (Misc queries) | |||
Creating a New Macro | Excel Discussion (Misc queries) | |||
Creating Macro | Excel Discussion (Misc queries) | |||
Need Help Creating A Macro | Excel Worksheet Functions |