Need help with a forcasting type problem

[Brake-Man2008]

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.

 

[Shane Devenshire]

Hi,

how is your data laid out?

Cheers,
Shane Devenshire

 

[Shane Devenshire]

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]

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.

 

[Shane Devenshire]

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
=C2>C1

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]

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.