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!