Is there a way to eliminate specific cells from formulas?

I am creating a table showing daily and weekly averages of Production lines.
I want to create a weekly chart but I dont want the weekly totals to include
the daily sum and averages. Is there a way to eliminate those cells from the
weekly totals?

Check the SUBTOTAL function. It ignores other SUBTOTAL functions in its
formula range.

1st argument options:
Func_Num Function
1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Valerian" wrote:

> I am creating a table showing daily and weekly averages of Production lines.
> I want to create a weekly chart but I dont want the weekly totals to include
> the daily sum and averages. Is there a way to eliminate those cells from the
> weekly totals?

yes, if you mean that by using subtotal instead of sum it will ignore all
other subtotal functions, that will help a lot. Thanks
Are you any good at writing formulas?

"Ron Coderre" wrote:

> Check the SUBTOTAL function. It ignores other SUBTOTAL functions in its
> formula range.
>
> 1st argument options:
> Func_Num Function
> 1 AVERAGE
> 2 COUNT
> 3 COUNTA
> 4 MAX
> 5 MIN
> 6 PRODUCT
> 7 STDEV
> 8 STDEVP
> 9 SUM
> 10 VAR
> 11 VARP
>
>
> Does that help?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Valerian" wrote:
>
> > I am creating a table showing daily and weekly averages of Production lines.
> > I want to create a weekly chart but I dont want the weekly totals to include
> > the daily sum and averages. Is there a way to eliminate those cells from the
> > weekly totals?

>> Are you any good at writing formulas?<<

Um...yeah...I've written a few. Why?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Valerian" wrote:

> yes, if you mean that by using subtotal instead of sum it will ignore all
> other subtotal functions, that will help a lot. Thanks
> Are you any good at writing formulas?
>
> "Ron Coderre" wrote:
>
> > Check the SUBTOTAL function. It ignores other SUBTOTAL functions in its
> > formula range.
> >
> > 1st argument options:
> > Func_Num Function
> > 1 AVERAGE
> > 2 COUNT
> > 3 COUNTA
> > 4 MAX
> > 5 MIN
> > 6 PRODUCT
> > 7 STDEV
> > 8 STDEVP
> > 9 SUM
> > 10 VAR
> > 11 VARP
> >
> >
> > Does that help?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "Valerian" wrote:
> >
> > > I am creating a table showing daily and weekly averages of Production lines.
> > > I want to create a weekly chart but I dont want the weekly totals to include
> > > the daily sum and averages. Is there a way to eliminate those cells from the
> > > weekly totals?

Well I am trying to write a formula for a production line to determine the
number of minutes it takes to complete each unit. I am currently using start
time(G2) and stop time(E2) less lunches(H2) and line stops(I2) (for parts
shortages) quantity of units being built and the number of stations per
production line. I use ((G2-E2)-(H2)-(I2) for total time then I calculate
total time per unit per station in hours M2/(K2+(L2-1)) where M2 is Total
Time and K2 is quantity and L2 is number of stations on the line. Then I
convert this answer to minutes per station then to minutes per unit.

I do get the numbers I am looking for even though this seems complicated.
What I am wondering is if there are standard production variations say for
breaks and setup that I am not including in the formula. Additionally if you
know a better way to calculate minutes per station and minutes per unit I am
open to suggestions

"Ron Coderre" wrote:

> >> Are you any good at writing formulas?<<
>
> Um...yeah...I've written a few. Why?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Valerian" wrote:
>
> > yes, if you mean that by using subtotal instead of sum it will ignore all
> > other subtotal functions, that will help a lot. Thanks
> > Are you any good at writing formulas?
> >
> > "Ron Coderre" wrote:
> >
> > > Check the SUBTOTAL function. It ignores other SUBTOTAL functions in its
> > > formula range.
> > >
> > > 1st argument options:
> > > Func_Num Function
> > > 1 AVERAGE
> > > 2 COUNT
> > > 3 COUNTA
> > > 4 MAX
> > > 5 MIN
> > > 6 PRODUCT
> > > 7 STDEV
> > > 8 STDEVP
> > > 9 SUM
> > > 10 VAR
> > > 11 VARP
> > >
> > >
> > > Does that help?
> > >
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP-Pro
> > >
> > >
> > > "Valerian" wrote:
> > >
> > > > I am creating a table showing daily and weekly averages of Production lines.
> > > > I want to create a weekly chart but I dont want the weekly totals to include
> > > > the daily sum and averages. Is there a way to eliminate those cells from the
> > > > weekly totals?

Can you give a couple examples of the data in the referenced cells?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Valerian" wrote:

> Well I am trying to write a formula for a production line to determine the
> number of minutes it takes to complete each unit. I am currently using start
> time(G2) and stop time(E2) less lunches(H2) and line stops(I2) (for parts
> shortages) quantity of units being built and the number of stations per
> production line. I use ((G2-E2)-(H2)-(I2) for total time then I calculate
> total time per unit per station in hours M2/(K2+(L2-1)) where M2 is Total
> Time and K2 is quantity and L2 is number of stations on the line. Then I
> convert this answer to minutes per station then to minutes per unit.
>
> I do get the numbers I am looking for even though this seems complicated.
> What I am wondering is if there are standard production variations say for
> breaks and setup that I am not including in the formula. Additionally if you
> know a better way to calculate minutes per station and minutes per unit I am
> open to suggestions
>
> "Ron Coderre" wrote:
>
> > >> Are you any good at writing formulas?<<
> >
> > Um...yeah...I've written a few. Why?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "Valerian" wrote:
> >
> > > yes, if you mean that by using subtotal instead of sum it will ignore all
> > > other subtotal functions, that will help a lot. Thanks
> > > Are you any good at writing formulas?
> > >
> > > "Ron Coderre" wrote:
> > >
> > > > Check the SUBTOTAL function. It ignores other SUBTOTAL functions in its
> > > > formula range.
> > > >
> > > > 1st argument options:
> > > > Func_Num Function
> > > > 1 AVERAGE
> > > > 2 COUNT
> > > > 3 COUNTA
> > > > 4 MAX
> > > > 5 MIN
> > > > 6 PRODUCT
> > > > 7 STDEV
> > > > 8 STDEVP
> > > > 9 SUM
> > > > 10 VAR
> > > > 11 VARP
> > > >
> > > >
> > > > Does that help?
> > > >
> > > > ***********
> > > > Regards,
> > > > Ron
> > > >
> > > > XL2002, WinXP-Pro
> > > >
> > > >
> > > > "Valerian" wrote:
> > > >
> > > > > I am creating a table showing daily and weekly averages of Production lines.
> > > > > I want to create a weekly chart but I dont want the weekly totals to include
> > > > > the daily sum and averages. Is there a way to eliminate those cells from the
> > > > > weekly totals?

Work Order # Product LI# Start date Start Time Stop date End
100233464 UPE-6 2 3/31 6.00 3/31 11.50 0.00 1.50 ALL INTERNAL
PTS 10 4 4.00 0.31 18.46 73.85
100232198 UPE-6 2 3/31 10.75 3/31 13.50 0.50 1 4 2.25 0.56 33.75 135.00


"Ron Coderre" wrote:

> Can you give a couple examples of the data in the referenced cells?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Valerian" wrote:
>
> > Well I am trying to write a formula for a production line to determine the
> > number of minutes it takes to complete each unit. I am currently using start
> > time(G2) and stop time(E2) less lunches(H2) and line stops(I2) (for parts
> > shortages) quantity of units being built and the number of stations per
> > production line. I use ((G2-E2)-(H2)-(I2) for total time then I calculate
> > total time per unit per station in hours M2/(K2+(L2-1)) where M2 is Total
> > Time and K2 is quantity and L2 is number of stations on the line. Then I
> > convert this answer to minutes per station then to minutes per unit.
> >
> > I do get the numbers I am looking for even though this seems complicated.
> > What I am wondering is if there are standard production variations say for
> > breaks and setup that I am not including in the formula. Additionally if you
> > know a better way to calculate minutes per station and minutes per unit I am
> > open to suggestions
> >
> > "Ron Coderre" wrote:
> >
> > > >> Are you any good at writing formulas?<<
> > >
> > > Um...yeah...I've written a few. Why?
> > >
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP-Pro
> > >
> > >
> > > "Valerian" wrote:
> > >
> > > > yes, if you mean that by using subtotal instead of sum it will ignore all
> > > > other subtotal functions, that will help a lot. Thanks
> > > > Are you any good at writing formulas?
> > > >
> > > > "Ron Coderre" wrote:
> > > >
> > > > > Check the SUBTOTAL function. It ignores other SUBTOTAL functions in its
> > > > > formula range.
> > > > >
> > > > > 1st argument options:
> > > > > Func_Num Function
> > > > > 1 AVERAGE
> > > > > 2 COUNT
> > > > > 3 COUNTA
> > > > > 4 MAX
> > > > > 5 MIN
> > > > > 6 PRODUCT
> > > > > 7 STDEV
> > > > > 8 STDEVP
> > > > > 9 SUM
> > > > > 10 VAR
> > > > > 11 VARP
> > > > >
> > > > >
> > > > > Does that help?
> > > > >
> > > > > ***********
> > > > > Regards,
> > > > > Ron
> > > > >
> > > > > XL2002, WinXP-Pro
> > > > >
> > > > >
> > > > > "Valerian" wrote:
> > > > >
> > > > > > I am creating a table showing daily and weekly averages of Production lines.
> > > > > > I want to create a weekly chart but I dont want the weekly totals to include
> > > > > > the daily sum and averages. Is there a way to eliminate those cells from the
> > > > > > weekly totals?

Start Time Stop date End Time Lunch Line Stop Qty Stns
6.00 3/31 11.50 0.00 1.50 10 4
10.75 3/31 13.50 0.50 1 4

I use military time and decimals ie: 13.50 = 1:30pm & 10.75 = 10:45am

"Ron Coderre" wrote:

> Can you give a couple examples of the data in the referenced cells?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Valerian" wrote:
>
> > Well I am trying to write a formula for a production line to determine the
> > number of minutes it takes to complete each unit. I am currently using start
> > time(G2) and stop time(E2) less lunches(H2) and line stops(I2) (for parts
> > shortages) quantity of units being built and the number of stations per
> > production line. I use ((G2-E2)-(H2)-(I2) for total time then I calculate
> > total time per unit per station in hours M2/(K2+(L2-1)) where M2 is Total
> > Time and K2 is quantity and L2 is number of stations on the line. Then I
> > convert this answer to minutes per station then to minutes per unit.
> >
> > I do get the numbers I am looking for even though this seems complicated.
> > What I am wondering is if there are standard production variations say for
> > breaks and setup that I am not including in the formula. Additionally if you
> > know a better way to calculate minutes per station and minutes per unit I am
> > open to suggestions
> >
> > "Ron Coderre" wrote:
> >
> > > >> Are you any good at writing formulas?<<
> > >
> > > Um...yeah...I've written a few. Why?
> > >
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP-Pro
> > >
> > >
> > > "Valerian" wrote:
> > >
> > > > yes, if you mean that by using subtotal instead of sum it will ignore all
> > > > other subtotal functions, that will help a lot. Thanks
> > > > Are you any good at writing formulas?
> > > >
> > > > "Ron Coderre" wrote:
> > > >
> > > > > Check the SUBTOTAL function. It ignores other SUBTOTAL functions in its
> > > > > formula range.
> > > > >
> > > > > 1st argument options:
> > > > > Func_Num Function
> > > > > 1 AVERAGE
> > > > > 2 COUNT
> > > > > 3 COUNTA
> > > > > 4 MAX
> > > > > 5 MIN
> > > > > 6 PRODUCT
> > > > > 7 STDEV
> > > > > 8 STDEVP
> > > > > 9 SUM
> > > > > 10 VAR
> > > > > 11 VARP
> > > > >
> > > > >
> > > > > Does that help?
> > > > >
> > > > > ***********
> > > > > Regards,
> > > > > Ron
> > > > >
> > > > > XL2002, WinXP-Pro
> > > > >
> > > > >
> > > > > "Valerian" wrote:
> > > > >
> > > > > > I am creating a table showing daily and weekly averages of Production lines.
> > > > > > I want to create a weekly chart but I dont want the weekly totals to include
> > > > > > the daily sum and averages. Is there a way to eliminate those cells from the
> > > > > > weekly totals?

Wouldn't Time-per-Unit-per-Station be this?:
=TotalTime/Qty/Stns
=M2/K2/L2
=4.00/10/4
=0.10

Or...am I missing something?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Valerian" wrote:

> Start Time Stop date End Time Lunch Line Stop Qty Stns
> 6.00 3/31 11.50 0.00 1.50 10 4
> 10.75 3/31 13.50 0.50 1 4
>
> I use military time and decimals ie: 13.50 = 1:30pm & 10.75 = 10:45am
>
> "Ron Coderre" wrote:
>
> > Can you give a couple examples of the data in the referenced cells?
> >
> > ***********
> > Regards,
> > Ron
> >
> > XL2002, WinXP-Pro
> >
> >
> > "Valerian" wrote:
> >
> > > Well I am trying to write a formula for a production line to determine the
> > > number of minutes it takes to complete each unit. I am currently using start
> > > time(G2) and stop time(E2) less lunches(H2) and line stops(I2) (for parts
> > > shortages) quantity of units being built and the number of stations per
> > > production line. I use ((G2-E2)-(H2)-(I2) for total time then I calculate
> > > total time per unit per station in hours M2/(K2+(L2-1)) where M2 is Total
> > > Time and K2 is quantity and L2 is number of stations on the line. Then I
> > > convert this answer to minutes per station then to minutes per unit.
> > >
> > > I do get the numbers I am looking for even though this seems complicated.
> > > What I am wondering is if there are standard production variations say for
> > > breaks and setup that I am not including in the formula. Additionally if you
> > > know a better way to calculate minutes per station and minutes per unit I am
> > > open to suggestions
> > >
> > > "Ron Coderre" wrote:
> > >
> > > > >> Are you any good at writing formulas?<<
> > > >
> > > > Um...yeah...I've written a few. Why?
> > > >
> > > > ***********
> > > > Regards,
> > > > Ron
> > > >
> > > > XL2002, WinXP-Pro
> > > >
> > > >
> > > > "Valerian" wrote:
> > > >
> > > > > yes, if you mean that by using subtotal instead of sum it will ignore all
> > > > > other subtotal functions, that will help a lot. Thanks
> > > > > Are you any good at writing formulas?
> > > > >
> > > > > "Ron Coderre" wrote:
> > > > >
> > > > > > Check the SUBTOTAL function. It ignores other SUBTOTAL functions in its
> > > > > > formula range.
> > > > > >
> > > > > > 1st argument options:
> > > > > > Func_Num Function
> > > > > > 1 AVERAGE
> > > > > > 2 COUNT
> > > > > > 3 COUNTA
> > > > > > 4 MAX
> > > > > > 5 MIN
> > > > > > 6 PRODUCT
> > > > > > 7 STDEV
> > > > > > 8 STDEVP
> > > > > > 9 SUM
> > > > > > 10 VAR
> > > > > > 11 VARP
> > > > > >
> > > > > >
> > > > > > Does that help?
> > > > > >
> > > > > > ***********
> > > > > > Regards,
> > > > > > Ron
> > > > > >
> > > > > > XL2002, WinXP-Pro
> > > > > >
> > > > > >
> > > > > > "Valerian" wrote:
> > > > > >
> > > > > > > I am creating a table showing daily and weekly averages of Production lines.
> > > > > > > I want to create a weekly chart but I dont want the weekly totals to include
> > > > > > > the daily sum and averages. Is there a way to eliminate those cells from the
> > > > > > > weekly totals?

Yes thanks.

"Ron Coderre" wrote:

> Wouldn't Time-per-Unit-per-Station be this?:
> =TotalTime/Qty/Stns
> =M2/K2/L2
> =4.00/10/4
> =0.10
>
> Or...am I missing something?
>
> ***********
> Regards,
> Ron
>
> XL2002, WinXP-Pro
>
>
> "Valerian" wrote:
>
> > Start Time Stop date End Time Lunch Line Stop Qty Stns
> > 6.00 3/31 11.50 0.00 1.50 10 4
> > 10.75 3/31 13.50 0.50 1 4
> >
> > I use military time and decimals ie: 13.50 = 1:30pm & 10.75 = 10:45am
> >
> > "Ron Coderre" wrote:
> >
> > > Can you give a couple examples of the data in the referenced cells?
> > >
> > > ***********
> > > Regards,
> > > Ron
> > >
> > > XL2002, WinXP-Pro
> > >
> > >
> > > "Valerian" wrote:
> > >
> > > > Well I am trying to write a formula for a production line to determine the
> > > > number of minutes it takes to complete each unit. I am currently using start
> > > > time(G2) and stop time(E2) less lunches(H2) and line stops(I2) (for parts
> > > > shortages) quantity of units being built and the number of stations per
> > > > production line. I use ((G2-E2)-(H2)-(I2) for total time then I calculate
> > > > total time per unit per station in hours M2/(K2+(L2-1)) where M2 is Total
> > > > Time and K2 is quantity and L2 is number of stations on the line. Then I
> > > > convert this answer to minutes per station then to minutes per unit.
> > > >
> > > > I do get the numbers I am looking for even though this seems complicated.
> > > > What I am wondering is if there are standard production variations say for
> > > > breaks and setup that I am not including in the formula. Additionally if you
> > > > know a better way to calculate minutes per station and minutes per unit I am
> > > > open to suggestions
> > > >
> > > > "Ron Coderre" wrote:
> > > >
> > > > > >> Are you any good at writing formulas?<<
> > > > >
> > > > > Um...yeah...I've written a few. Why?
> > > > >
> > > > > ***********
> > > > > Regards,
> > > > > Ron
> > > > >
> > > > > XL2002, WinXP-Pro
> > > > >
> > > > >
> > > > > "Valerian" wrote:
> > > > >
> > > > > > yes, if you mean that by using subtotal instead of sum it will ignore all
> > > > > > other subtotal functions, that will help a lot. Thanks
> > > > > > Are you any good at writing formulas?
> > > > > >
> > > > > > "Ron Coderre" wrote:
> > > > > >
> > > > > > > Check the SUBTOTAL function. It ignores other SUBTOTAL functions in its
> > > > > > > formula range.
> > > > > > >
> > > > > > > 1st argument options:
> > > > > > > Func_Num Function
> > > > > > > 1 AVERAGE
> > > > > > > 2 COUNT
> > > > > > > 3 COUNTA
> > > > > > > 4 MAX
> > > > > > > 5 MIN
> > > > > > > 6 PRODUCT
> > > > > > > 7 STDEV
> > > > > > > 8 STDEVP
> > > > > > > 9 SUM
> > > > > > > 10 VAR
> > > > > > > 11 VARP
> > > > > > >
> > > > > > >
> > > > > > > Does that help?
> > > > > > >
> > > > > > > ***********
> > > > > > > Regards,
> > > > > > > Ron
> > > > > > >
> > > > > > > XL2002, WinXP-Pro
> > > > > > >
> > > > > > >
> > > > > > > "Valerian" wrote:
> > > > > > >
> > > > > > > > I am creating a table showing daily and weekly averages of Production lines.
> > > > > > > > I want to create a weekly chart but I dont want the weekly totals to include
> > > > > > > > the daily sum and averages. Is there a way to eliminate those cells from the
> > > > > > > > weekly totals?