Help with creating a Macro

[Liz J]

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!

 

[Sheeloo]

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,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...

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!

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!

 

[ShaneDevenshire]

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$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

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]

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?

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,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...

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!

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!

 

[Sheeloo]

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?

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?

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,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...

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!

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]

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!

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$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

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]

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

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?

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?

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,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...
:

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!

:

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!