Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
Valerian
 
Posts: n/a
Default 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?
  #2   Report Post  
Posted to microsoft.public.excel.newusers
Ron Coderre
 
Posts: n/a
Default Is there a way to eliminate specific cells from formulas?

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?

  #3   Report Post  
Posted to microsoft.public.excel.newusers
Valerian
 
Posts: n/a
Default Is there a way to eliminate specific cells from formulas?

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?

  #4   Report Post  
Posted to microsoft.public.excel.newusers
Ron Coderre
 
Posts: n/a
Default Is there a way to eliminate specific cells from formulas?

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?

  #5   Report Post  
Posted to microsoft.public.excel.newusers
Valerian
 
Posts: n/a
Default Is there a way to eliminate specific cells from formulas?

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?



  #6   Report Post  
Posted to microsoft.public.excel.newusers
Ron Coderre
 
Posts: n/a
Default Is there a way to eliminate specific cells from formulas?

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?

  #7   Report Post  
Posted to microsoft.public.excel.newusers
Valerian
 
Posts: n/a
Default Is there a way to eliminate specific cells from formulas?

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?

  #8   Report Post  
Posted to microsoft.public.excel.newusers
Valerian
 
Posts: n/a
Default Is there a way to eliminate specific cells from formulas?

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?

  #9   Report Post  
Posted to microsoft.public.excel.newusers
Ron Coderre
 
Posts: n/a
Default Is there a way to eliminate specific cells from formulas?

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?

  #10   Report Post  
Posted to microsoft.public.excel.newusers
Valerian
 
Posts: n/a
Default Is there a way to eliminate specific cells from formulas?

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?

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I copy only cells with formulas in another row? Soozy Excel Worksheet Functions 2 October 21st 05 08:02 PM
how do i protect cells in a shared worksheet Debi Excel Discussion (Misc queries) 3 September 30th 05 11:15 PM
keep specific cells from being changed. bronsonbits Excel Discussion (Misc queries) 1 September 7th 05 06:44 PM
how get rid of cells with unused formulas Benj Excel Discussion (Misc queries) 3 July 8th 05 02:47 PM
In Exel 2000, stop the blank cells (with formulas) from printing. tonyoc Excel Discussion (Misc queries) 1 December 10th 04 01:38 AM


All times are GMT +1. The time now is 11:08 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"