Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a forcasting type problem
I am trying to have excel do a few automatic calculations for me. In the
problem I am trying to solve I have the following: Given: Month (day 1-31) production each day (ranges from 0-3000 value is linked from another file) life of a tool (consider 10,000 before it needs changed) Find: Have a cell change color using (format cell) when tool life is up. I have been able to do this for an entire month worth of projected production (dividing it evenly into the number of production days) but have not been able to use a forcast funtion. I really do not care what funtion I use to do this just that I can expand this into a larger project with multiple tools (say 75) each with the posibility of differnt life expectancies. Thanks for any help someone can offer. -- Brake-Man2008 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a forcasting type problem
Hi,
how is your data laid out? Cheers, Shane Devenshire "Brake-Man2008" wrote: I am trying to have excel do a few automatic calculations for me. In the problem I am trying to solve I have the following: Given: Month (day 1-31) production each day (ranges from 0-3000 value is linked from another file) life of a tool (consider 10,000 before it needs changed) Find: Have a cell change color using (format cell) when tool life is up. I have been able to do this for an entire month worth of projected production (dividing it evenly into the number of production days) but have not been able to use a forcast funtion. I really do not care what funtion I use to do this just that I can expand this into a larger project with multiple tools (say 75) each with the posibility of differnt life expectancies. Thanks for any help someone can offer. -- Brake-Man2008 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a forcasting type problem
Hi,
Lets suppose your day runs from B1:B31: To conditionally format your cell(s): In 2003: 1. Select the cell you want to format 2. Choose Format, Conditional Formatting 3. Choose Formula is from the first drop down 4. In the second box enter the formula: =SUM(B$1:B1)10000 5. Click the Format button 6. Choose a color on the Patterns tab (or any available option) 7. Click OK twice. In 2007: 1. Highlight all the cells on the rows you want formatted 2. Choose Home, Conditional Formatting, New Rule 3. Choose Use a formula to determine which cell to format 4. In the Format values where this formula is true enter the following formula: =SUM(B$1:B1)10000 5. Click the Format button and choose a format. 6. Click OK twice If this helps, please click the Yes button. Cheers, Shane Devenshire "Brake-Man2008" wrote: I am trying to have excel do a few automatic calculations for me. In the problem I am trying to solve I have the following: Given: Month (day 1-31) production each day (ranges from 0-3000 value is linked from another file) life of a tool (consider 10,000 before it needs changed) Find: Have a cell change color using (format cell) when tool life is up. I have been able to do this for an entire month worth of projected production (dividing it evenly into the number of production days) but have not been able to use a forcast funtion. I really do not care what funtion I use to do this just that I can expand this into a larger project with multiple tools (say 75) each with the posibility of differnt life expectancies. Thanks for any help someone can offer. -- Brake-Man2008 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a forcasting type problem
the production numbers usually reach above the tool life, sometimes for
examplet: 50,000 production for month with 5000 tool life for a total of 10 changes in the month. The method you were explaining will light up the day it goes over 10,000 as well as all the days after that value. I can send an example of the layout that I have in excel if you want. -- Sandu Nagy "Brake-Man2008" wrote: I am trying to have excel do a few automatic calculations for me. In the problem I am trying to solve I have the following: Given: Month (day 1-31) production each day (ranges from 0-3000 value is linked from another file) life of a tool (consider 10,000 before it needs changed) Find: Have a cell change color using (format cell) when tool life is up. I have been able to do this for an entire month worth of projected production (dividing it evenly into the number of production days) but have not been able to use a forcast funtion. I really do not care what funtion I use to do this just that I can expand this into a larger project with multiple tools (say 75) each with the posibility of differnt life expectancies. Thanks for any help someone can offer. -- Brake-Man2008 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a forcasting type problem
Hi,
Suppose the life is 1000 using for my following example and your number of units for any day are located in column B. In C2 or the second cell of any blank column, enter the following formula =IF(INT(SUM(B$1:B2)/1000)C1,INT(SUM(B$1:B2)/1000),C1) Select the whichever column you want to format, lets say column B. Select from B2:B1000 (start with the second row.) Choose use the conditional formatting steps I suggested in the last post but change the formula to =C2C1 This will format a cell on any day that reaches 1000 new units producted. If this helps, please click the Yes button. Cheers, Shane Devenshire "Brake-Man2008" wrote: the production numbers usually reach above the tool life, sometimes for examplet: 50,000 production for month with 5000 tool life for a total of 10 changes in the month. The method you were explaining will light up the day it goes over 10,000 as well as all the days after that value. I can send an example of the layout that I have in excel if you want. -- Sandu Nagy "Brake-Man2008" wrote: I am trying to have excel do a few automatic calculations for me. In the problem I am trying to solve I have the following: Given: Month (day 1-31) production each day (ranges from 0-3000 value is linked from another file) life of a tool (consider 10,000 before it needs changed) Find: Have a cell change color using (format cell) when tool life is up. I have been able to do this for an entire month worth of projected production (dividing it evenly into the number of production days) but have not been able to use a forcast funtion. I really do not care what funtion I use to do this just that I can expand this into a larger project with multiple tools (say 75) each with the posibility of differnt life expectancies. Thanks for any help someone can offer. -- Brake-Man2008 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Need help with a forcasting type problem
This worked fine for what I was doing, but I have to take one more posibility
into account. For instance: I set up columb B as production totals per day numbered from 1-31. The problem that might occur is that the machine malfunctions or part gets misloaded resulting in a broken tool. This is were my question stemms, can I also force the counter for the tool life to start on a certain day? If the mishap happens on on day 5 of the month, what might the formula look like then? I did use the formula that you gave me as a model for the chart (had to make quite a few modifications) but worked like a charm. "Shane Devenshire" wrote: Hi, Suppose the life is 1000 using for my following example and your number of units for any day are located in column B. In C2 or the second cell of any blank column, enter the following formula =IF(INT(SUM(B$1:B2)/1000)C1,INT(SUM(B$1:B2)/1000),C1) Select the whichever column you want to format, lets say column B. Select from B2:B1000 (start with the second row.) Choose use the conditional formatting steps I suggested in the last post but change the formula to =C2C1 This will format a cell on any day that reaches 1000 new units producted. If this helps, please click the Yes button. Cheers, Shane Devenshire "Brake-Man2008" wrote: the production numbers usually reach above the tool life, sometimes for examplet: 50,000 production for month with 5000 tool life for a total of 10 changes in the month. The method you were explaining will light up the day it goes over 10,000 as well as all the days after that value. I can send an example of the layout that I have in excel if you want. -- Sandu Nagy "Brake-Man2008" wrote: I am trying to have excel do a few automatic calculations for me. In the problem I am trying to solve I have the following: Given: Month (day 1-31) production each day (ranges from 0-3000 value is linked from another file) life of a tool (consider 10,000 before it needs changed) Find: Have a cell change color using (format cell) when tool life is up. I have been able to do this for an entire month worth of projected production (dividing it evenly into the number of production days) but have not been able to use a forcast funtion. I really do not care what funtion I use to do this just that I can expand this into a larger project with multiple tools (say 75) each with the posibility of differnt life expectancies. Thanks for any help someone can offer. -- Brake-Man2008 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Chart type problem | Charts and Charting in Excel | |||
Forcasting for a multiyear period | Excel Discussion (Misc queries) | |||
how do I multiply dollars by networkdays for forcasting run rates | Excel Worksheet Functions | |||
Data Type Problem? | Excel Discussion (Misc queries) | |||
Custom Type Charts Problem | Excel Worksheet Functions |